Here are examples of VBA MACRO codes that allows to send emails within Ms Excel. It is not a rocket science to understand how to send the email using MS outlook via VBA code. Basically I use Excel to list the e-mail addresses and the name of the attachments.
Here is MACRO to send mail via CDO
Sub CDO_Send_Selection_Or_Range_Body()
Dim rng As Range
Dim iMsg As Object
Dim iConf As Object
Dim strto As String
Dim cell As Range
' Dim Flds As Variant
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "Gmail address"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Gmail password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With
Dim strbody As String
For Each cell In Range("G1:N1")
strbody = strbody & cell.Value & vbNewLine
Next
For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
r = cell.Row
For Each dn In Range("G" & r & ":" & "N" & r) ' or Range(Cells(r, 7), Cells(r, 13))
strbody = strbody & dn.Value & vbNewLine
Next
If cell.Offset(0, 1).Value <> "" Then
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then
Set iMsg = CreateObject("CDO.Message")
With iMsg
Set .Configuration = iConf
.To = cell.Value
.From = """Sender name"" "
.Subject = "Reminder"
.TextBody = "Dear " & Cells(cell.Row, "A").Value & "," & vbNewLine & vbNewLine & strbody & "Regards,"
.Send
End With
Set iMsg = Nothing
End If
End If
strbody = ""
Next cell
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
Whenever macro sends out an e-mail,via MS outlook you have to authorize it Select “yes” to authorize it . Below is code for sending out e-mail with and attachment.
Here is a example file with 8 Macros with different modules. Download here To view VBA press ALT+F11 button.
Change the mail address and try the examples if you have a account in Outlook Express or Windows Mail |
If you want to send any attachment with the email here is the tutorial file that you must read along with VBA