Attach PDF & Excel send email - Old Coding

macro_newbie

New Member
Joined
Dec 25, 2010
Messages
19
Hi Guys,

I am just trying to use an old macro, which ceates a new email, with some text and attatchments.

Unfortunatley its a very old Macro, and i am recieving an error

"Run-time error '-2147467259 (8004005)': There must be at least one name or distribution list in the To, Cc, or Bcc box."

I was hoping for some assistance please:eeek:

please feel free to suggest better coding.

Thanks in advance everyone..:confused:

Code:
Sub mail_regions2()
 For i = 19 To 100
 Dim week, file, email_sub, sender_name, filename

Message = vbCrLf & _
"Dear Sir/Madam" & vbCrLf & vbCrLf & _
"Please find attached the remittance advice for your most recent expenditure made at grant level, in both PDF and Excel format for your convenience." & vbCrLf & vbCrLf & _
"Kind Regards," & vbCrLf & vbCrLf 

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

filename = Trim(Sheets("macro").Range("b" & i))
file = "D:\Temp\Rem\" & filename & ".pdf"

filename = Trim(Sheets("macro").Range("b" & i))
file1 = "D:\Temp\Rem\" & filename & ".xls"

email_sub = "Remittance Advice " & filename & " - " & Range("c" & i)
Subject = email_sub

Sheets("macro").Select
With objOutlookMsg
.To = Range("d" & i)
.CC = Range("e" & i)
.Subject = Subject
.Body = Message
.Attachments.Add (file)
.Attachments.Add (file1)
.Send
End With

Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Next
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Sheets("Macro").Select
End Sub
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Forum statistics

Threads
1,224,576
Messages
6,179,639
Members
452,935
Latest member
mm1t1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top