Excel VBA Change From email address with IBM Notes?

Question

I have the following vba code which sends an email from excel using IBM Notes.

However, i want to be able to change the from address. Pleas can someone show me where i am going wrong?

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("M:M")) Is Nothing Then
    If Target.Cells.Count < 3 Then


  'Set up the objects required for Automation into lotus notes

    Dim Ref As String
    Dim TrueRef As String



    Ref = Range("H" & (ActiveCell.Row)).Value

    If Ref = "WSM" Then
    TrueRef = "WES"
    Else
    If Ref = "NAY" Then
    TrueRef = "NAY"
    Else
    If Ref = "ENF" Then
    TrueRef = "ENF"
    Else
    If Ref = "LUT" Then
    TrueRef = "MAG"
    Else
    If Ref = "NFL" Then
    TrueRef = "NOR"
    Else
    If Ref = "RUN" Then
    TrueRef = "RUN"
    Else
    If Ref = "SOU" Then
    TrueRef = "SOU"
    Else
    If Ref = "SOU" Then
    TrueRef = "SOU"
    Else
    If Ref = "BRI" Then
    TrueRef = "BRI"
    Else
    If Ref = "LIV" Then
    TrueRef = "LIV"
    Else
    If Ref = "BEL" Then
    TrueRef = "BEL"
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If




    ''''''''''''''''''''''''''''''''''

    Dim nMailBody As String
    Dim nMailSubject As String
    Dim nMailRecipient As Variant
    Dim nMail As Object
    Dim nSession As Object
    Dim nDatabase As Object
    Dim nMime As Object
    Dim nMailStream As Object
    Dim nChild As Object
    Dim nSomeMailBodyText As String
    Dim amountOfRecipients As Integer

    nSomeMailBodyText = "<p>Hello,</p><br><p>How are you?</p>"


    nMailSubject = "A great email"

    Set nSession = CreateObject("Notes.NotesSession")
    Set nDatabase = nSession.GETDATABASE("", "")
    Call nDatabase.OPENMAIL
    Set nMail = nDatabase.CREATEDOCUMENT




    nMail.Principal = "[email protected]"

    nMail.SendTo = "[email protected]"
    nMail.subject = "This is test"

    nSession.CONVERTMIME = False
    Set nMime = nMail.CREATEMIMEENTITY
    Set nMailStream = nSession.CREATESTREAM


    'vBody contient le texte au format Html
    Call nMailStream.WRITETEXT(nSomeMailBodyText)
    Call nMailStream.WRITETEXT(" - and again - ")
    Call nMailStream.WRITETEXT(nSomeMailBodyText)
    Call nMailStream.WRITETEXT("<br>")
    Call nMailStream.WRITETEXT("<br>")



    '----- READ AND PASTE SIGNATURE -------------------------------------

    'Get the standard signature location
    nSignatureLocation = nDatabase.GETPROFILEDOCUMENT("CalendarProfile").GETITEMVALUE("Signature")(0)



    Set nChild = nMime.CREATECHILDENTITY
    Call nChild.SETCONTENTFROMTEXT(nMailStream, "text/html;charset=iso-8859-1", ENC_NONE)
    Call nMailStream.Close
    nSession.CONVERTMIME = True



    'Send the document
    nMail.PostedDate = Now() 'Gets the mail to appear in the sent items folder
    nMail.SEND 0, Recipient




    End If
End If

End Sub

Karl-Henry Martinsson Karl-Henry Martinsson · Accepted Answer · 2017-01-11T00:42:50

As Richard already said, you can not spoof the sender from the client like that unless you use that undocumented method. I have a Notes class for mail notifications (can be found on my blog), but Richard is correct in that you as a beginner (which is pretty clear based on the code you posted) probably not should attempt using that method.

On a side note, why do you use such a convoluted way to set the value of TrueRef? Can't you use a Select Case statement? Or even just simplify your code:

TrueRef = Ref
If Ref = "WSM" Then
    TrueRef = "WES"
ElseIf Ref = "LUT" Then
    TrueRef = "MAG"
ElseIf Ref = "NFL" Then
    TrueRef = "NOR"
End If

or

If Ref = "WSM" Then
    TrueRef = "WES"
ElseIf Ref = "LUT" Then
    TrueRef = "MAG"
ElseIf Ref = "NFL" Then
    TrueRef = "NOR"
Else
    TrueRef = Ref
End If

Excel VBA Change From email address with IBM Notes?

2 Answers