vbaemailms-accessoutlook

Send message with multiple lines in Outlook email


I am trying to send Outlook email using Microsoft Access.

In the email, I want three cells of data.

Email
Pin
FirstName

I also would like to change the "From" email address from my default address to that of a secondary address I maintain.

Private Sub Command7_Click()

    Dim Msg As String
    Dim strFrom As String
    Dim strSubject As String
    
    Msg = "Dear " & FirstName & ",<P> &" _
    "Below is your Personal Identification Number."<P> &" _
    "<P> &" _
    & Pin "<P> &" _
    "This PIN is unique to you."<P> &" _
    "<P> &" _
    "You must safeguard, and not let anyone have access to your pin."<P> &" _
    "<P> &" _
    "To initiate a wire, have your PIN available and call us at 555-555-5555."<P> &" _
    "<P> &" _
    "Questions? Please reply to this email, or call us at 555-555-5555."
    
    Dim O As Outlook.Application
    Dim M As Outlook.MailItem
    
    Set O = New Outlook.Application
    Set M = O.CreateItem(olMailItem)
    
    strFrom = "MyDepartment@MyCompany.com"
    strSubject = "ENCRYPT - Personal Identification Number (PIN)"
    
    With M
        .BodyFormat = olFormatHTML
        .HTMLBody = Msg
        .To = Email
        .From = strFrom
        .Subject = strSubject
        .Display
    
    End With
    
    Set M = Nothing
    Set O = Nothing

End Sub

If I remark everything except the first line of the Msg, leaving

Msg = "Dear " & FirstName & ",<P> &" _

it will generate an email with the correct information, with the exception of the "From" field in the email.

When I add lines to my Msg string, I get various errors, including Syntax errors, and expected: end of statement errors.


Solution

  • Your string concatenation is a hot mess. You've got extra quotes all over the place. I think that part of what's creating your confusion is the excessive use of line continuation trying to emulate what the text looks like in your email. Try this as single line of code:

    Msg = "Dear " & FirstName & ",\<P\> Below is your Personal Identification Number.\<P\>\<P\> & Pin & "\<P\>This PIN is unique to you.\<P\>\<P\> You must safeguard, and not let anyone have access to your pin.\<P\>\<P\>To initiate a wire, have your PIN available and call us at 555-555-5555.\<P\>\<P\>Questions? Please reply to this email, or call us at 555-555-5555."
    

    If you get this to work, then add in the line continuation if you want to. Personally, I rarely use line continuation, since it just adds complexity that usually isn't needed.