Outlook mail with attachment using vba

[Anupam2710]

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

 

[bobhc]

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

..

 

[Monty]

Hello Anupam.

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

Monty!

 

[Anupam2710]

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

 

[Monty]

Sorry Missed the link!

https://www.rondebruin.nl/win/s1/outlook/mail.htm

 

[Monty]

Hello Anupam.

Please provide a sample file to try!

Monty!

 

[Monty]

Hello Anupam.

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

Try this!

option Explicit
Sub SendMail()
   
    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem
    Dim blRunning As boolean
   
    'get application
    blRunning=True
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then
        Set olApp = New Outlook.Application
        blRunning=False
    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