Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Tips for the Mac Outlook mail examples

You can use the tips below to change the Outlook attachments examples on my site : Send Mail from Mac Excel

Change the Body text

You see that I use bodycontent:="Hi there" in the examples, but what if you want to add more text and use seperate lines?. By default Outlook 2011 create HTML mails so we must use <br> to start a new line.

You can add this to the macro before it call the function : MailFromMacwithOutlook

     Dim strbody As String
    strbody = "Hi there" & "<br>" & _
              "This is line 1" & "<br>" & _
              "This is line 2" & "<br>" & _
              "This is line 3" & "<br>" & _
              "This is line 4" & "<br>"

Or use cell values

     Dim strbody As String
    With ThisWorkbook.Sheets("Sheet1")
        strbody = "Hi there" & "<br>" & _
                  .Range("A1") & "<br>" & _
                  .Range("A2") & "<br>" & _
                  .Range("A3") & "<br>" & _
                  .Range("A4")
    End With

Or use a loop through a range

     Dim Cell As Range
    Dim strbody As String
    For Each Cell In ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
        strbody = strbody & Cell.Value & "<br>"
    Next

When you want to use this you must change the bodycontent line in the function call to bodycontent:=strbody

 

To, CC, BCC and add Attachments

When you use the new MailFromMacwithOutlook function created on 19 Aug 2013 you have the option to add more people in the To, CC, BCC and add more attachments.

 toaddress:="ron@debruin.nl,jelle@debruin.nl"

You must seperate the mail addresses with a comma, this is working the same for CC and BCC addresses. Or you can use mail addresses that are in a range.

     Dim cell As Range
    Dim strto As String
    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
        If cell.Value Like "?*@?*.?*" Then
            strto = strto & cell.Value & ","
        End If
    Next cell
    If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)

When you use this you must use toaddress:=strto in the function call

 

Add more then one attachment to your mail. This is working the same as in the To, CC, BCC lines, the full file path of each file must be seperated by a comma.

Note: In the code examples I attach the activeworkbook with .FullName. If you want to add another file like I show you below you must start with a comma in the string of the second file. If you not do this it will see both files as one big string and will not attach both files to your mail.

attachment:=.FullName & ",Macintosh HD:Users:mvp:Desktop:TestMap:Workbook1.xlsx"