This macro assumes an Excel file (CSV, XLS*) is structured in the following format, although you can easily change the code to fit your format. The subject is the same for all messages, however, it could come from the spreadsheet instead.
Column A: Email address for the To field
Column B: Email address for the CC field (if used)
Column c: Email address for the BCC field (if used)
Column D: First line of body
Column E: Second line in body
To use this macro, prepare and save an Excel file then paste the code in Outlook and run it. The macro will ask you to select the Excel file then create the messages.
Sub CreateMeetingsfromCSV() Dim xlApp As Object 'Excel.Application Dim xlWkb As Object ' As Workbook Dim xlSht As Object ' As Worksheet Dim rng As Object 'Range Dim objMsg As MailItem 'Set xlApp = New Excel.Application Set xlApp = CreateObject("Excel.Application") strFilepath = xlApp.GetOpenFilename If strFilepath = False Then xlApp.Quit Set xlApp = Nothing Exit Sub End If Set xlWkb = xlApp.Workbooks.Open(strFilepath) Set xlSht = xlWkb.Worksheets(1) Dim iRow As Integer Dim iCol As Integer iRow = 2 iCol = 1 While xlSht.Cells(iRow, 1) <> "" Set objMsg = Application.CreateItem(olMailItem) With objMsg .To = xlSht.Cells(iRow, 1) .CC= xlSht.Cells(iRow, 2) .BCC = xlSht.Cells(iRow, 3) .Subject = "This is the subject" ' send plain text message, use olFormatHTML for formatted mail .BodyFormat = olFormatPlain ' can use 2 or more cells to create the body ' use .HTMLBody if you want to format words .Body = xlSht.Cells(iRow, 4) & vbCRLF & xlSht.Cells(iRow, 5) .Importance = olImportanceHigh .Display ' .Send to automatically send it End With iRow = iRow + 1 Wend xlWkb.Close xlApp.Quit Set xlWkb = Nothing Set xlApp = Nothing End Sub
You can format text within the messages by using HTML format and HTML tags. The tags can go with the spreadsheet text:
This is the order number: 12345
Or, to format the text in one cell or the entire message, wrap the text in HTML tags, as shown in the example below.
.BodyFormat = olFormatHTML .HTMLBody = xlSht.Cells(iRow, 4) & vbCRLF & "<em>" & xlSht.Cells(iRow, 5) & "</em>"
How to use macros
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor
Why can't the subject line come from the spreadsheet as well?
It can - the person who had the question that triggered the article was doing a mail merge and needed one subject. Reference the cell in the same way it's used for the other fields - if you want something like "order #1234 was shipped" you can do it like this:
.subject = "Order #" & xlSht.Cells(iRow, 4) & " was shipped". You can put the body together in the same manner - use vbcrlf for new lines.
Hi there :)
I have a small question: Is it possible to not choose .BodyFormat = olFormatPlain and instead send an e-mail using Rich text?
I was wondering if it was possible to change the font size of the body of the e-mail.
Thank you for the easy to understand e-mail.
You don't want to use rich text (RTF) with non-Outlook (they'll get plain text) or internet email addresses (they might get html, they might get plain text). You would use HTML format, and it should be set in the email, not converted from RTF to HTML by outlook.
You can change the font in the message body if using HTML. (the format should go before the body line too) It's easiest if you are changing the entire message, not just a section. Use olformathtml and set the font size using word vba commands - a sample is here: https://www.slipstick.com/developer/word-macro-apply-formatting-outlook-email/ It's also possible to use HTML tags when constructing the body - either use html tags around words in the spreadsheet (Column d: This is the # number: <b>12345</b>) or in the code (sample below) - and use HTMLBody
.htmlBody = xlSht.Cells(iRow, 4) & vbCRLF & "<em>" & xlSht.Cells(iRow, 5) & "</em>"