sending mail with excel and pdf attachment in outlook by VB

[srinivasanyadhav]

Dear Team,

can you help me in sending a mail to my collegues by attaching PDF and Excel file through VB?
have to send seperate mails to everyone with a message body.

[Rudi]

My VB skills in Outlook is not so hot, so the code on this page should give you a starting point.

Oh... I see you posted in the Outlook forum, but your title refers to emailing with Excel.
in that regard, you will find useful code to use from Ron de Bruin's site here...

[HansV]

Do you want to send to a group of contacts, or do you have a list of email addresses in for example an Excel workbook?

[srinivasanyadhav]

Yes. I have a list of E-mail addresses in an excel workbook.

The attachments (one excel and one pdf) is in a folder.
I want those sheets to be attached in an E-mail and want to send it to each contact seperately.

[HansV]

Here is a macro you can run from Excel:

Code: Select all

Sub SendMessages()
    Const c = 3 ' column with email addresses
    Const strXL = "C:\Excel\MyWorkbook.xlsx"
    Const strPDF = "C:\PDF\MyPDF.pdf"

    Dim olApp As Object
    Dim olMsg As Object
    Dim r As Long
    Dim m As Long

    On Error Resume Next
    Set olApp = GetObject(Class:="Outlook.Application")
    If olApp Is Nothing Then
        Set olApp = CreateObject(Class:="Outlook.Application")
    End If
    On Error GoTo ErrHandler

    m = Cells(Rows.Count, c).End(xlUp).Row
    For r = 2 To m
        Set olMsg = olApp.CreateItem(0) ' 0 = olMailItem
        olMsg.To = Cells(r, c).Value
        olMsg.Subject = "My Subject"
        olMsg.Body = "My Email Body"
        olMsg.Attachments.Add strXL
        olMsg.Attachments.Add strPDF
        olMsg.Display ' or .Send if you want to send it immediately
    Next r

ExitHandler:
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

Change the constants and the text strings as needed. The code works best if Outlook is already running.

[srinivasanyadhav]

Many Thanks. It works good!