Macros

[coleyoder]

Hello,

We created a macro to send out personalized emails to our customers with each individual email having an attachment of that certain customers invoice. When running the macro I seem to keep getting stuck where it says : MyAttachments.Add(attachment). Not sure if there is some way to fix this if anyone has any advice.

Thanks.

 

[Kamolga]

Can you paste the macro?

 

[coleyoder]

Sub SendEmailwithAttachment()


'This macro creates and sends an email for each recipient in a list with a personalized subject, body and attached file.
'Column 1 = first name
'Column 2 = last name
'Column 3 = company
'Column 4 = email address
'Column 5 = subject
'Column 6 = body
'Column 7 = file path
'Column 8 = file name


Dim OutlookApp As Outlook.Application
Dim OutlookMailItem As Outlook.MailItem
Dim MyAttachments As Outlook.Attachments
Dim filename As String
Dim path As String
Dim attachment As String




For i = 2 To Worksheets("Automated Email List").Cells(Rows.Count, 1).End(xlUp).Row
For x = 3 To Worksheets("Automated Email List").Cells(Rows.Count, 1).End(xlUp).Row
Set OutlookApp = New Outlook.Application
Set OutlookMailItem = OutlookApp.CreateItem(0)
Set MyAttachments = OutlookMailItem.Attachments
path = Worksheets("Automated Email List").Cells(x, 7).Value


filename = Worksheets("Automated Email List").Cells(i, 8)
attachment = path + filename


OutlookMailItem.To = Cells(i, 4).Value
OutlookMailItem.Subject = Cells(i, 5).Value
OutlookMailItem.Body = Cells(i, 6).Value
MyAttachments.Add (attachment)
OutlookMailItem.Display
OutlookMailItem.Send


Set OutlookMailItem = Nothing
Set OutlookApp = Nothing
Exit For
Exit For
Next x
Next i




End Sub

 

[Kamolga]

Sub SendEmailwithAttachment()


'This macro creates and sends an email for each recipient in a list with a personalized subject, body and attached file.
'Column 1 = first name
'Column 2 = last name
'Column 3 = company
'Column 4 = email address
'Column 5 = subject
'Column 6 = body
'Column 7 = file path
'Column 8 = file name


Dim OutlookApp As Outlook.Application
Dim OutlookMailItem As Outlook.MailItem
Dim MyAttachments As Outlook.Attachments
Dim filename As String
Dim path As String
Dim attachment As String
'On Error Resume Next
Set OutlookApp = New Outlook.Application
    For i = 2 To Worksheets("Automated Email List").Cells(Rows.Count, 1).End(xlUp).Row
       
        Set OutlookMailItem = OutlookApp.CreateItem(0)
        Set MyAttachments = OutlookMailItem.Attachments
        path = Worksheets("Automated Email List").Cells(i, 7).Value
        
        filename = Worksheets("Automated Email List").Cells(i, 8)
        attachment = path & filename
        
        OutlookMailItem.To = Cells(i, 4).Value
        OutlookMailItem.Subject = Cells(i, 5).Value
        OutlookMailItem.Body = Cells(i, 6).Value
        MyAttachments.Add (attachment)
        OutlookMailItem.Display
        'OutlookMailItem.Send
            
    Next i
End Sub

 

[coleyoder]

Used that Macro but for some reason it is still stopping at the "MyAttachments.Add (attachment)"

Not sure why I've checked the files and they seemed to be named right on excel

 

[Kamolga]

It works with mine.

In my path (G) cells I used a path ending with \

[TABLE="width: 189"]
<tbody>[TR]
[TD="width: 189"]C:\Users\MyName\Documents\[/TD]
[/TR]
</tbody>[/TABLE]

And in my file () I put the name with extension

Script2.pdf

 

[Kamolga]

If you take out the ' before

[COLOR=#333333]'On Error Resume Next[/COLOR]

the mail will be displayed without attacment if it can't...so you could maybe investigate the ones that have and the ones that have not