VBA mail with multiple attachment.

[patil prakash]

Hello Team,

I am new here.

I want to attache pdf file in below code. the pdf link available in cells(i, 14).

could you please help me for solve this query.




Sub SendMultipleEmails()

Dim Mail_Object, OutApp As Variant


With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For I = 2 To lastrow

Set Mail_Object = CreateObject("Outlook.Application")
Set OutApp = Mail_Object.CreateItem(0)

With OutApp
.To = Cells(I, 8)
.cc = Cells(I, 10)
.Display
.Subject = "Shipment Delivery Notification"
.htmlbody = "<b>Dear Customer,</b>" & _
"<p>Kind Attn :- ""<b>" & Cells(I, 7) & "</b>" & ", Company - ""<b>" & Cells(I, 5) & "</b>" & _
"<p>Thank you for your recent order ""<b>" & Cells(I, 6) & "</b>" & " to us." & vbNewLine & vbNewLine & _
"<p>We are pleased to inform you that the items listed are now on the way to you." & vbNewLine & _
"<p>Your order has been executed against Sales Order ""<b>" & Cells(I, 1) & "</b>" & ", & Invoiced with ""<b>" & Cells(I, 2) & "</b>" & ", dated ""<b>" & Cells(I, 3) & "</b>" & ". Materials have been shipped to your address through Transporter-""<b>" & Cells(I, 13) & "</b>" & ", against docket no.- ""<b>" & Cells(I, 11) & " </b>" & " dated ""<b>" & Cells(I, 12) & "</b>" & vbNewLine & _
"<p>Your order should reach you within Few days as mentioned in the website of the Transporter." & vbNewLine & _
"<P>It has been a pleasure to serve you, and please do not hesitate to get in touch with the contact person ""<b>" & Cells(I, 9) & "</b>" & " Email:- ""<b>" & Cells(I, 10) & "</b>" & " if the materials are not delivered in time." & vbNewLine & _
"<P>This email is generated by the system - do not reply to this address." & vbNewLine & _
"<P>" & vbNewLine & _
"<p>It has been a pleasure to serve you," & vbNewLine & _
"<P>Again, thank you for choosing our product." & .htmlbody


End With

Next I

debugs:
If Err.Description <> "" Then MsgBox Err.Description

End Sub

 

[Domenic]

You can use the Add method of the Attachments collection for the MailItem object...

With OutApp
    .To = Cells(I, 8)
    .cc = Cells(I, 10)
    'etc
    '
    '
    .attachments.add Cells(I,14)
End with

However, here are some suggestions...

Option Explicit You should declare all your variables. If you include the Option Explicit statement at the top of the module, it will force you to explicitly declare all your variables. And, it will catch any spelling mistakes.

On Error GoTo You should include an On Error statement at the beginning of your code, along with proper error handling to deal with any potential errors.

CreateObject You only need to create one instance of Outlook, so create it before your For/Next loop.

Variable Names Consider naming the variables for your Outlook application and mail item to something that's more appropriate. Otherwise, it could be confusing.

Here's your code, which has been amended accordingly...

Option Explicit

Sub SendMultipleEmails()

    On Error GoTo errorHandler

    Dim lastRow As Long
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    Dim olApp As Object
    Set olApp = CreateObject("Outlook.Application")
    
    Dim olMailItem As Object
    Dim i As Long
    For i = 2 To lastRow
        Set olMailItem = olApp.CreateItem(0)
        With olMailItem
            .Display
            .to = Cells(i, 8).Value
            .cc = Cells(i, 10).Value
            .Subject = "Shipment Delivery Notification"
            .htmlbody = "...."
            .Attachments.Add Cells(i, 14).Value
            '.Send
        End With
    Next i
    
exitHandler:
    Set olMailItem = Nothing
    Set olApp = Nothing

    Exit Sub
    
errorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error"
    Resume exitHandler

End Sub

Hope this helps!

 

[patil prakash]

hello sir,

thanks for your prompt help, but its not attaching PDF file in mail.

error 5: Invalid procedure call or argument.

could you please help for the above error.

 

[Domenic]

Which line causes the error? Is it this one?

.Attachments.Add Cells(i, 14).Value

If so, run the code again. This time, when you get the error, see what value Cells(i, 14).Value returns? It should return a valid path and filename. Does it?

 

[patil prakash]

Yes, Its work.
thank you so much sir.
I have 2-3 files in the same folder & same name but by sequence.(etc.- MTC, MTC-2, MTC-3) can we add these all files in one mail? & if their is no file then also mail should go without error.

 

[Domenic]

Yes, Its work.
thank you so much sir.
I have 2-3 files in the same folder & same name but by sequence.(etc.- MTC, MTC-2, MTC-3) can we add these all files in one mail? & if their is no file then also mail should go without error.

You're very welcome!

For your new question, though, please start a new thread.

Cheers!

 

[patil prakash]

I did it.

thanks for your valuable support.

Cheers!