VBA to Send E-Mail

**NeedForExcel** · 04-21-2013, 11:57 PM

I found a code on a Website which helps send E-mail (File Attached).

I Just had 3 Queries.

1) We do not use Outlook here at office. We use some other E-Mail client (Zimbra). Can we configure that instead of Outlook.

2) Assume if I restructure the sheet so that the E-Mail Address, CC & Bcc are in 1 row, and I have multiple rows with different customers E-Mail ids, (However Subject Line & Message Text remaining same) is it possible to create a loop so that it will send all the mails on its own?

3) Can we include attachments. For example If I have a few excel sheets in a folder, all attachments having a unique name can some logic be applied to accah a particular file an E-Mail is being send to that specific E-Mail id to which the attachment is to be sent?

**gb#** · 04-22-2013, 10:55 AM

I've come up against the same problem, and after Googling around for a while found that CDO reference is what I was looking for. Try searching for "send email in VBA with CDO" or something like that.

The following code connects to a gmail account (for example), and send the email without it being displayed (therefore doesn't rely on Outlook)

Sub SendEmail()
    
    Dim CDO_Mail_Object As Object
    Dim CDO_Config As Object
    Dim SMTP_Config As Variant
    Dim Email_Send_From, Email_Send_To, Email_Cc, Email_Bcc, Email_Subject, Email_Body As String
    
    Email_Subject = Range("B4")
    Email_Send_From = Range("B5")
    Email_Send_To = Range("B1")
    Email_Cc = Range("B2")
    Email_Bcc = Range("B3")
    Email_Body = Range("B6")
    
    Set CDO_Mail_Object = CreateObject("CDO.Message")
    On Error GoTo debugs
    Set CDO_Config = CreateObject("CDO.Configuration")
    CDO_Config.Load -1
    Set SMTP_Config = CDO_Config.Fields
    With SMTP_Config
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'NTLM method
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 587
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "gmail_password"
        .Update
    End With
    With CDO_Mail_Object
    Set .Configuration = CDO_Config
    End With
    CDO_Mail_Object.Subject = Email_Subject
    CDO_Mail_Object.From = Email_Send_From
    CDO_Mail_Object.To = Email_Send_To
    CDO_Mail_Object.TextBody = Email_Body
    CDO_Mail_Object.cc = Email_Cc 'Use if needed
    CDO_Mail_Object.bcc = Email_Bcc 'Use if needed
    CDO_Mail_Object.Send

debugs:
    If Err.Description <> "" Then MsgBox Err.Description

End Sub