Solved

use excel vba to send an excel range + attachment by email through outlook

Posted on 2010-11-10
23
1,445 Views
Last Modified: 2012-05-10
I have been using the code below to send emails with attachments through outlook out of excel. it works fine if i enter body text and email recipients in the code. However, I would much prefer to use cell values as recipients or body text for the email. Unfortunately, I have not been very successful at it yet.

If I run the code, the error message "There must be at least one name or distribution list in the To, Cc or Bcc box." appears.

Neither .Recipients.Add(Range("MailTo")) nor .Recipients.Add(MailTo) works...

Any help appreciated.

I use Office 2003.
Sub SendPdfInvoice()
   'send pdf invoice

   Sheets("invoice").Select
   Range("MailBody").Select
   
   Dim MailToAdress As String
    MailToAdress = Range("MailTo").Value
   Dim MailBccAdress As String
    MailCcAdress = Range("MailCc").Value
   Dim MailSubject As String
    MailSubject = Range("MailSubject").Value
   


' creates and sends a new e-mail message with Outlook

Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
Dim ToContact As Outlook.Recipient
    Set OLF = GetObject("", _
        "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    Set olMailItem = OLF.Items.Add ' creates a new e-mail message
    With olMailItem
        .Subject = Range("MailSubject").Value ' message subject
        'Set ToContact = .Recipients.Add("james@bond.com") ' add a recipient
        Set ToContact = .Recipients.Add(Range("MailTo")) ' add a recipient
        Set ToContact = .Recipients.Add(MailCcAddress) ' add a recipient
        ToContact.Type = olCC ' set latest recipient as CC
        .Body = "Also this text I would like to replace with the range in the excel sheet named MailBody instead of writing it here" & Chr(13)
        ' the message text with a line break
        .Attachments.Add Range("PdfPath").Value, olByValue, , _
            "Attachment" ' insert attachment
        .OriginatorDeliveryReportRequested = False ' delivery confirmation
        .ReadReceiptRequested = False ' read confirmation
        .Send ' sends the e-mail message (puts it in the Outbox)
    End With
    Set ToContact = Nothing
    Set olMailItem = Nothing
    Set OLF = Nothing

End Sub

Open in new window

0
Comment
Question by:stmoritz
  • 12
  • 6
  • 4
  • +1
23 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 34107662
I assume your name ranges are properly declare in your workbook. If not then first please check them.
Also try adding the sheet name on which these name ranges exists what i mean is this...
Saurabh...

MailToAdress = Sheets("Your Sheet Name here where this value exists").Range("MailTo").Value
   Dim MailBccAdress As String
    MailCcAdress = Sheets("Your Sheet Name here where this value exists"). Range("MailCc").Value

Open in new window

0
 

Author Comment

by:stmoritz
ID: 34108640
Thanks. Error still remains after changing as you suggested to

Dim MailToAdress As String
    MailToAdress = Sheets("invoice").Range("MailTo").Value
   Dim MailCcAdress As String
    MailCcAdress = Sheets("invoice").Range("MailCc").Value
   Dim MailSubject As String
    MailSubject = Sheets("invoice").Range("MailSubject").Value

And further below to

        Set ToContact = .Recipients.Add(MailToAddress) ' add a recipient



So somehow this way the value does not get from the range "MailTo" to "MailToAddress"

However, if changed to

        Set ToContact = .Recipients.Add(Range("MailToAddress")) ' add a recipient
        Set ToContact = .Recipients.Add(Range("MailCcAddress")) ' add a recipient
        ToContact.Type = olCC ' set latest recipient as CC

i get the error 1004: "Method 'Range' of object '_Global' failed

...?? any help appreciated, thanks a lot.





Dim MailToAdress As String
    MailToAdress = Sheets("invoice").Range("MailTo").Value
   Dim MailCcAdress As String
    MailCcAdress = Sheets("invoice").Range("MailCc").Value
   Dim MailSubject As String
    MailSubject = Sheets("invoice").Range("MailSubject").Value

Open in new window

0
 
LVL 45

Expert Comment

by:patrickab
ID: 34109955
stmoritz,

In the attached file you will find various different options. The simplest is the CDO email which uses the email addresses listed on Sheet1 - that macro responds to the button on that worksheet.

Hope it helps

Patrick
cdo-email-general-app-03.xls
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:stmoritz
ID: 34113583
if I change the To field in the code to

.To = Range("MailToAddress").Value

I have the same error message as in the code originally posted:

Run-time erro '1004'
Method 'Range' of object '_Gobal' failed

seems I'm not moving forward here or doing something wrong...
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34113616
stmoritz,

I have provided you with a working macro. Is there some reason that you want to modify it?

Patrick
0
 

Author Comment

by:stmoritz
ID: 34113732
Hi Patrick,

Thanks for your solution. However:

1) your macro does not work on my system. Error "The transport failed to connect to the server."

2) I can't see where it allows to attach an attachment with a path that is stored in a cell of the excel sheet, right?

3) to take To/Bcc/Subject etc. from cell values I need to adjust offset values, right?

Thanks.  
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34115015
stmoritz,

My apologies for not re-checking the macros - I have messed around with them so they need modification - my fault - I'll be back.

Meantime please confirm that you actually want to:

1. Send different files to different people

2. That all the files are in the same directory

Or is it that you want to:

3. Send the ActiveWorkbook to the recipients (without the VBA)?

I will wait for your answers...

Patrick
0
 

Author Comment

by:stmoritz
ID: 34116481
thanks Patrick

The code creates a pdf out of a range of the active sheet (this works)
 
The second part of the code than should send this pdf by email to a few recipients with text.

The code posted in the initial post works fine to do this and send it, but I have to enter recipients (To, Cc, Bcc), subject and message body in the code itself and I would like the code to take this information from cell values, so that I can use it more flexible and also for other workbooks/purposes.

So it should take the following values from different cell values:
  • Path for attachment (any type of file not particularly related to active workbook in this case pdf)
  • To
  • Cc
  • Bcc
  • Subject
  • Message body text
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34119765
stmoritz,

I'm afraid that your VBA code is 'alien' to me at it's Outlook rather than Excel-based. However perhaps you can put this whole section of VBA in a loop that iterates through the cells for the data that you need - perhaps like the first 3 emboldened lines, like this:

For i = 2 to lastrowofdata
    With olMailItem

        .Subject = Sheets("Sheet1").cells(i,"E") ' message subject
        Set ToContact = .Recipients.Add Sheets("Sheet1").cells(i,"E")  ' add a recipient
        Set ToContact = .Recipients.Add Sheets("Sheet1").cells(i,"F") ' add a recipient

'and so on...

        ToContact.Type = olCC ' set latest recipient as CC
        .Body = "Also this text I would like to replace with the range in the excel sheet named MailBody instead of writing it here" & Chr(13)
        ' the message text with a line break
        .Attachments.Add Range("PdfPath").Value, olByValue, , _
            "Attachment" ' insert attachment
        .OriginatorDeliveryReportRequested = False ' delivery confirmation
        .ReadReceiptRequested = False ' read confirmation
        .Send ' sends the e-mail message (puts it in the Outbox)
    End With
Next i

Patrick
0
 
LVL 13

Expert Comment

by:stefri
ID: 34125002
Hi
This is working for me (I commented out attachment)
Sub SendPdfInvoice()
   'send pdf invoice

   Sheets("invoices").Select
   Range("MailBody").Select
   
   Dim MailToAdress As String
    MailToAdress = Range("MailTo").Value
   Dim MailccAdress As String
    MailccAdress = Range("MailCc").Value
   Dim MailSubject As String
    MailSubject = Range("MailSubject").Value
   


' creates and sends a new e-mail message with Outlook

Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
Dim ToContact As Outlook.Recipient, ccRec As Outlook.Recipient
    Set OLF = GetObject("", _
        "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    Set olMailItem = OLF.Items.Add ' creates a new e-mail message
    With olMailItem
        .Subject = Range("MailSubject").Value ' message subject
         .Recipients.Add (Range("MailTo")) ' add a recipient
' I used another variable to store ccRecipient
        Set ccRec = .Recipients.Add(MailccAdress) ' add a recipient
        ccRec.Type = olCC   ' set latest recipient as CC
        .Body = "Also this text I would like to replace with the range in the excel sheet named MailBody instead of writing it here" & Chr(13)
        ' the message text with a line break
'        .Attachments.Add Range("PdfPath").Value, olByValue, , _
'            "Attachment" ' insert attachment
        .OriginatorDeliveryReportRequested = False ' delivery confirmation
        .ReadReceiptRequested = False ' read confirmation
        .Send ' sends the e-mail message (puts it in the Outbox)
    End With
    Set ccRec= Nothing
    Set olMailItem = Nothing
    Set OLF = Nothing

End Sub


hope this helps
stefri
0
 

Author Comment

by:stmoritz
ID: 34125283
thanks stefri. we sem to getting closer.

I get the error now  

Run-time error '-1456455675 (a9304005)':
Outlook does not recognize one or more names

after clicking on debug I end up at yellow selected
    .Send ' sends the e-mail message (puts it in the Outbox)
0
 
LVL 13

Expert Comment

by:stefri
ID: 34127038
are email addresses proprely formed?
I tried with a misformed email address (no @sign) and .Send crashes
Are you using email addresses or names that should be resolved prior sending
Stefri
0
 

Author Comment

by:stmoritz
ID: 34127291
Hi Stefri. this starts looking very, very good indeed!

If there is only one email address in the field MailCc or MailTo it works.

- How can I add another second cc address? other cell with new name and copy same code?
- Is there a way to replace the body text       .Body = "Also this text I would like to replace with the range in the excel sheet named MailBody instead of writing it here" & Chr(13) in the code also somehow to get it from a named range in the sheet?

Thanks for your appreciated help and time.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34127495
>- How can I add another second cc address? other cell with new name and copy same code?
- Is there a way to replace the body text       .Body = "Also this text I would like to replace with the range in the excel sheet named MailBody instead of writing it here" & Chr(13) in the code also somehow to get it from a named range in the sheet?

Please see my earlier comments as I believe I have already answered that in my Comment ID:34119765

Patrick
0
 
LVL 13

Expert Comment

by:stefri
ID: 34127560
Usually, outlook accepts addresses separated by semi-colons or
define a range MailTos (more than one cell) and loop through the number cells belongingto that range

About body, declare a named range bodyTosend and get though range("bodyToSend").value as you do for recipeint, ccrec, etc

look at the code below

Sub SendPdfInvoice()
   'send pdf invoice
Dim aCell As Object

   'Sheets("invoices").Select
   Range("MailBody").Select
   
   Dim MailToAdress As String
    MailToAdress = Range("MailTo").Value
   Dim MailccAdress As String
    MailccAdress = Range("MailCc").Value
   Dim MailSubject As String
    MailSubject = Range("MailSubject").Value
   Dim mailBody As String
   mailBody = Range("MailBody").Value


' creates and sends a new e-mail message with Outlook

Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
Dim ToContact As Outlook.Recipient, ccRec As Outlook.Recipient
    Set OLF = GetObject("", _
        "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    Set olMailItem = OLF.Items.Add ' creates a new e-mail message
    With olMailItem
        .Subject = Range("MailSubject").Value ' message subject
        For Each aCell In Range("mailtos").Cells
            If Not (IsEmpty(aCell)) Then
               .Recipients.Add aCell.Value ' add a recipient
            End If
         Next
' I used another variable to store ccRecipient
        Set ccRec = .Recipients.Add(MailccAdress) ' add a recipient
        ccRec.Type = olCC   ' set latest recipient as CC
        .Body = VBA.Replace(mailBody, Chr(10), Chr(13), 1, -1, vbTextCompare)
'        .Attachments.Add Range("PdfPath").Value, olByValue, , _
'            "Attachment" ' insert attachment
        .OriginatorDeliveryReportRequested = False ' delivery confirmation
        .ReadReceiptRequested = False ' read confirmation
        .Display ' sends the e-mail message (puts it in the Outbox)
    End With
    Set ccRec = Nothing
    Set olMailItem = Nothing
    Set OLF = Nothing

End Sub
0
 

Author Comment

by:stmoritz
ID: 34137645
Pretty weird. It works as long as I have only one email address in the MailTo or MailCc field, whether with comma or semicolon, it doesn't work...

Also the way to take the body text out of the sheet instead of the code does not work here with me. no text is in the email message and the email is being sent but not delivered, as the error message (in outlook inbox) undeliverable:

This message could not be sent. Try sending the message again later, or contact your network administrator.  Error is [0x80070057-00000000-00000000].

appears.

question to Stefri: does it work in your sheet and if yes, could you maybe post that sheet with the code?

question to Patrickab: sorry somehow I overlooked your solution ID: 34119765 - does it work for you and might it solve the problem to get the content of an excel range into the message body? sorry, I know you are more excel than outlook ;-)
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34138035
>...I overlooked your solution ID: 34119765 - does it work for you and might it solve the problem to get the content of an excel range into the message body? sorry, I know you are more excel than outlook ;-)

In Excel I cycle through cells containing the email addresses and for the body text and anything else that changes from one email to the next and it works well.

Patrick
0
 

Author Comment

by:stmoritz
ID: 34150680
Maybe posting the file with the current code might help as I dont' move forward.

Current code on my machine does not add cc and does not add mailbody. Furthermore, the sender name/email remains empty, so outlook does not send it out (see error in ID: 34137645).

any further help appreciated.
EE-invoice.xls
0
 

Author Comment

by:stmoritz
ID: 34158824
okay, nothing seems to move... now I try it the other way with another code

This code works and does exactly what it should, however, somehow I should add an attachment to this mail, that's the only thing missing...

Attachment path and file name is stored in the excel sheet range "PdfPath"

'        .Attachments.Add Range("PdfPath").Value, olByValue, , _
'            "Attachment" ' insert attachment


Sub SendPdfInvoice2()
   'send pdf invoice

   ' Select the range of cells on the active worksheet.
   Sheets("invoice").Select
   
   Range("MailBody").Select
   
   Dim MailToAddress As String
    MailToAddress = Range("MailTo").Value
   Dim MailCcAddress As String
    MailCcAddress = Range("MailCc").Value
   Dim MailSubject As String
    MailSubject = Range("MailSubject").Value

   
   ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True
   
   ' Set the optional introduction field thats adds
   ' some header text to the email body. It also sets
   ' the To and Subject lines. Finally the message
   ' is sent.
   With ActiveSheet.MailEnvelope
      .Item.To = MailToAddress
      .Item.CC = MailCcAddress
      .Item.Subject = MailSubject
      .Item.Send
   End With
   
   
End Sub

Open in new window

0
 

Accepted Solution

by:
stmoritz earned 0 total points
ID: 34160041
Google rules! After numerous web searches I finally was able to come to the following solution that works perfectly and fullfills all needs! all fields (To, Cc, Subject, MailBody) taken from ranges in the excel sheet and even the attachment (path taken also from excel sheet cell/range) works! Cool!


Sub SendPdfInvoice()
   'send pdf invoice


   ' Select the range of cells on the active worksheet.
   Sheets("invoice").Select
   
   Range("MailBody").Select
   
   Dim MailToAddress As String
    MailToAddress = Range("MailTo").Value
   Dim MailCcAddress As String
    MailCcAddress = Range("MailCc").Value
   Dim MailSubject As String
    MailSubject = Range("MailSubject").Value
   Dim strAttachment As String
   'strAttachment = "C:\customers.txt"
   strAttachment = Range("PdfPath").Value

   
   ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True
   
   ' Set the optional introduction field thats adds
   ' some header text to the email body. It also sets
   ' the To and Subject lines. Finally the message
   ' is sent.
   With ActiveSheet.MailEnvelope
      .Item.To = MailToAddress
      .Item.CC = MailCcAddress
      .Item.Subject = MailSubject
      .Item.Attachments.Add strAttachment
      .Item.Send
   End With
   
   
End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:stefri
ID: 34160226
stmoritz
I am disapointed you are closing the question as my post was doing what you wanted
Nevertheless, as a gift, I offer you spreadsheet with lot of corrections on Mailsubject and othernamed ranges not properly defined or missing
stefri stefri-EE-invoice.xlsm
0
 

Author Comment

by:stmoritz
ID: 34160765
Hi stefri. Thanks a lot, however, your solution did not do all that I was looking for and did not work correctly, as you can see also from post 34137645. The code I arrived at the end does everything I have been looking for perfectly and the way I want it and does it without any error or problem. But many thanks for the file, I will have a look at it and check it. Thanks a lot for your understanding.
0
 

Author Closing Comment

by:stmoritz
ID: 34186462
works perfect! exactly what i needed!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MS Outlook is a world-class email client application that is mainly used for e-communication globally.  In this article, we will discuss the basic idea about MS Outlook, its advanced features, and types of MS Outlook File formats.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now