Send emails to multiple addresses

Hello jenksie101,

Welcome to the Forum!

The following macro will need some modification to work on your system. This macro accesses a remote server that requires you to log on before the email can be sent. Since I use Gmail as my remote outgoing mail server, the outgoing server address is "smtp.gmail.com". You will need to include your email account , password, and outgoing mail server in the code. Any place where you will need to make a change will be marked in red font. If you have you own SMTP server then I will need to modify the code for you.

The emails are sent out using the CDO (Collaboration Data Object). There are no security messages nor confirmation dialogs. Because the email is delivered directly to the SMTP server, this will work with any mail client. There are 2 macros you will need to added to your workbook. The first is the macro to send the emails from the worksheet. The second is to activate sending the emails when the workbook is opened.

Macro to Send the Emails
You will need to include your email account and password in the macro. Look for words in red font.

'Written: November 12, 2009
'Author:  Leith Ross
'Summary: Send emails from addresses on a worksheet with using CDO. Email addresses
'         are in column "B", email sent flag in column "D", and account name in
'         column "E".


Sub SendEmailsUsingCDO()

  Dim cdoConfig As Object
  Dim cdoEmail As Object
  Dim cdoNameSpace As String
  Dim Email As Variant
  Dim EmailRng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("Sheet1")
    
    Set EmailRng = Wks.Range("B4")
    Set RngEnd = Wks.Cells(Rows.Count, EmailRng.Column).End(xlUp)
    Set EmailRng = IIf(RngEnd.Row < EmailRng.Row, EmailRng, Wks.Range(EmailRng, RngEnd))
    
     'Create the Collaboration Data Object
      Set cdoEmail = CreateObject("CDO.Message")
      Set cdoConfig = CreateObject("CDO.Configuration")
    
      cdoNameSpace = "http://schemas.microsoft.com/cdo/configuration/"
      cdoConfig.Load -1
    
     'Setup email to be sent using a remote server and authentication
      With cdoConfig.Fields
        .Item(cdoNameSpace & "smtpauthenticate") = 1
        .Item(cdoNameSpace & "sendusername") = "YourEmail@anymail.com"
        .Item(cdoNameSpace & "sendpassword") = "YourPassword"
        .Item(cdoNameSpace & "smtpserver") = "smtp.servername.com"
        .Item(cdoNameSpace & "sendusing") = 2
        .Item(cdoNameSpace & "smtpserverport") = 465
        .Item(cdoNameSpace & "sendusessl") = True
        .Update
      End With
    
      Set cdoEmail.Configuration = cdoConfig
      
      For Each Email In EmailRng
       'Check if email has been sent
        If StrComp(Trim(Email.Offset(0, 2)), "sent", 1) <> 0 Then
          'Send the email
           With cdoEmail
             .To = Email.Text
             .Subject = "Insurance Premium Adjustment"
             .TextBody = "Dear Team," & vbCrLf _
                       & "  Please chase premium adjustment for " _
                       & Email.Offset(0, 3).Text & "." & vbCrLf & "Thank you. "
             .Send
           End With
          'Mark email as sent
           Email.Offset(0, 2) = "Sent"
        End If
      Next Email
      
   'Free the objects and the memory used
    Set cdoConfig = Nothing
    Set cdoEmail = Nothing

End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Macro to Run when the Workbook Opens

Private Sub Workbook_Open()
  Call SendEmailsUsingCDO
End Sub

How to Save a Workbook Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on any Worksheet's Name Tab
3. Left Click on View Code in the pop up menu.
4. Press ALT+F11 keys to open the Visual Basic Editor.
5. Press CTRL+R keys to shift the focus to the Project Explorer Window
6. Press the Down Arrow Key until ThisWorkbook is highlighted in blue.
7. Press the Enter key to move the cursor to the Code Window
8. Paste the macro code using CTRL+V
9. Save the macro in your Workbook using CTRL+S

Hello jenksie101,

When sending emails using the CDO, you have to use the correct port number. In my case, Gmail is my outgoing mail server (SMTP). Gmail requires me log on first. You haven't told me how your email is setup or if you know what your outgoing port number is.

Discovering your Email Port Numbers using Outlook 2003

1. Open Outlook
2. Press the keys CTRL+ALT+S
3. This will display the dialog box titled Send/Receive Groups
4. Select All Accounts and Click the button labeled Edit...
5. This will display the dialog box titled Send/Receive Settings - All Accounts
6. Select the account that you are using and click the Account Properties... button.
7. This will bring up the dialog box titled Internet E-mail Settings
8. Click the tab labeled Advanced
9. This tab will show your sever port numbers and if the server requires an encrypted connection (checkbox is checked).

Let me know what your settings are. I can then make any needed adjustments to the macro code.

Hello jenksie101,

Thanks for the followup information. I haven't done this with Exchange before. I will need to do some research on using CDO with Exchange.