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
please feel free to suggest better coding.
Thanks in advance everyone..
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
please feel free to suggest better coding.
Thanks in advance everyone..
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