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