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.
lTo")) nor .Recipients.Add(MailTo) works...
Any help appreciated.
I use Office 2003.
'send pdf invoice
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("", _
Set olMailItem = OLF.Items.Add ' creates a new e-mail message
.Subject = Range("MailSubject").Value ' message subject
'Set ToContact = .Recipients.Add("email@example.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)
Set ToContact = Nothing
Set olMailItem = Nothing
Set OLF = Nothing