1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Outlook mail with attachment using vba

Discussion in 'VBA Macros' started by Anupam2710, Apr 20, 2017.

  1. Anupam2710

    Anupam2710 New Member

    I am planning to send hundreds of emails (with excel attachment) from a shared mailbox with email content to be picked from word.

    I reached as far as being able to send a mail using vba but it picks my primary email in outlook to send mails (not the shared mailbox which is also included in my outlook). I was unable to pick the content from word.. was successful in picking html text.

    As a start i understand that i will need an excel sheet with 4 things
    1. Persons name who will receive email
    2. Persons email id
    3. Word file path (content to be pasted in the body of email)
    4. Excel file path (excel to be attached)

    Could you please guide

    Post moved by moderator
    Last edited by a moderator: Apr 20, 2017
  2. bobhc

    bobhc Excel Ninja

    If you took a moment to read the rules you would have read this bit.

    • Abusive language will not be tolerated.
    • Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out.
    • Cross-Posting. Generally, it is considered poor practic
  3. Monty

    Monty Well-Known Member

    Hello Anupam.

    Here is the best which i refer for outlook with excel vba..Very useful.

  4. Anupam2710

    Anupam2710 New Member

    Thanks Monty ! Is it a link you wanted to share.. sorry can't see it here.
  5. Monty

    Monty Well-Known Member

  6. Monty

    Monty Well-Known Member

    Hello Anupam.

    Please provide a sample file to try!

  7. Monty

    Monty Well-Known Member

    Hello Anupam.

    Not really sure your excel format...Just guessing...

    Try this!

    Code (vb):
    option Explicit
    Sub SendMail()
        Dim olApp As Outlook.Application
        Dim olMail As Outlook.MailItem
        Dim blRunning As boolean
        'get application
        On Error Resume Next
        Set olApp = GetObject(, "Outlook.Application")
        If olApp Is Nothing Then
            Set olApp = New Outlook.Application
        End If
        On Error Goto 0
        Set olMail = olApp.CreateItem(olMailItem)
        With olMail
            'Specify the email subject
           .Subject = "My email with attachment"
            'Specify who it should be sent to
           'Repeat this line to add further recipients
           .Recipients.Add "Monty@Gmail.com"
            'specify the file to attach
           'repeat this line to add further attachments
           .Attachments.Add "c:\test.xlsx"
            'specify the text to appear in the email
           .Body = "Here is an email"
            'Choose which of the following 2 lines to have commented out
           .Display 'This will display the message for you to check and send yourself
           '.Send ' This will send the message straight away
       End With
        If Not blRunning Then olApp.Quit
        Set olApp=Nothing
        Set olMail=Nothing
    End Sub

Share This Page