Help on mail

[singh]

Hi to all

Scenerio: I have a sheet with full of mailing address and name of that
particular person and a word document in C drive.

Question: Can I create a macro for sending mail to all the person in the
excel sheet with name as salutation in mail and attach the word document?
Column A contains Name of person
Column B contains Email ID
For sending mail to a particular person I generally use the below code:
Sub SendActiveWorkbook()
ActiveWorkbook.SendMail _
Recipients:="(e-mail address removed)", _
Subject:="As per the mail content " & Format(Date, "dd/mmm/yy")
End Sub
This is only for the active sheet. I do not know how to attach word document
through macro.

Thanks in advance

 

[ND Pard]

Try the following:

Sub SendExcelEmailWithDocument()
Dim r As Integer
Dim attPath As String
Dim strRecipient As String
Dim strBody As String
Dim appOut As Object
Dim outMail As Object

For r = 1 To 1 ' put your current rows in here as r for exampl 2 To 25
ActiveSheet.Cells(r, 1).Select

attPath = "C:\Temp\InsideMSAccess_200701.pdf" 'your attachment if
needed

strRecipient = Range("A1").Value
strBody = "I hope this works..."

Set appOut = CreateObject("Outlook.Application")
appOut.Session.Logon
Set outMail = appOut.CreateItem(0)

With outMail
.To = ActiveCell.Offset(0, 1).Value 'assumes email address is in
Col 2
.Subject = "This is an Excel email test"
.Body = strRecipient & vbCr & vbCr & strBody
.Attachments.Add (attPath)
'.display
'use display and comment out .send if you want to review email prior to
sending it
.send
End With
Next r

Set outMail = Nothing
Set appOut = Nothing

MsgBox "The macro has completed!", vbInformation, "Done!"
End Sub

Good Luck