top of page

How to "vba email cc"?

Please see the following code for creating several emails with VBA.

vba send emails automatically

Step 1 before inserting this code into you VBE (Alt + F11) is to enable the library reference for Outlook so that you have access to Outlook classes for the below code.

Press (Alt + F11) keys, Go to Tools, References, and make sure "Microsoft Outlook 16.0 Object Library". if you have an earlier or later version than 16.0 that should be fine as well.

How my worksheet looks like

send emails from worksheet and attach files from folder


Sub SendThoseEmails()

Application.ScreenUpdating = False 'Turns off screenupdating for faster script.

Dim msg As String 'Email message.

'Imports the Outlook class and library references.
Dim OutApp As Outlook.Application
Set OutApp = New Outlook.Application

For Each Cell In Range("A2:A10") 'Iterates up to cell A10.
    If Cell <> "" Then
        'Creating the Email here.
        Dim OutMail As Outlook.MailItem
        Set OutMail = OutApp.CreateItem(olMailItem)
        'Email message
        msg = Email_Sheet.Range("I1") 
        'Replaces the name in each email more a more 
        'personalized touch.
        msg = Replace(msg, "NAME", Cell) 
        msg = Replace(msg, "(NEW)", "<br/>") 
        'Converting to HTML code as that is what 
        'we draft the message in.
        msg = Replace(msg, "(NEWL)", "<br/><br/>") 
        'Converting to HTML code as that is 
        'what we draft the message in.

        With OutMail
            .Display 'This displays the message on screen.
            .To = Cell.Offset(0, 1) 'Picks up the email address.
            .CC = "" 'add .CC to CC in the email. 
            .BCC = "" 'add .BCC to BCC in the email. 
            .Subject = "PlsFixThx"
            .HTMLBody = msg & OutMail.HTMLBody
            .Save 'Change this to .Send for sending the 
            'message instead of displaying it.

        End With
    End If
Next Cell

Application.ScreenUpdating = True 'Turns on screenupdating 
                                   now that we are done.

MsgBox ("Done")

End Sub

Download Excel Workbook here.

Feel free to download my prepared workbook containing this exact code to get a jumpstart on your project.

Download ZIP • 24KB

Learn more about VBA here for all my posts:

Learn more about Python here for all my posts:

If you have found this article or website helpful. Please show your support by visiting the shop below.

47 views0 comments


bottom of page