Unable to send Bulk emails through excel VBA

Question

I have written a code to send bulk email to all recipients through a list from excel. however, it does not attach the attachment and also it does not send the document. in case we bypass the attachment, it only creates the email but does not send the email.

Sub Sendemail()

Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim myAttachment As Outlook.Attachment
Dim lastrow As Long

lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

With olMail
.To = Cells(i, 1).Value
.Subject = Cells(i, 2).Value
.Body = Cells(i, 3).Text
.Attachments.Add Cells(i, 4)
.Display
.Send
End With

Set olMail = Nothing
Set olApp = Nothing

Next i

End Sub

Excel file image

the above code should send the emails directly to the recipients.

You asked two questions which make this post less useful in this Q & A. You could ask a separate question about attaching files with a different file path format than https://learn.microsoft.com/en-us/office/vba/api/outlook.attachments.add. — niton, May 12 '23 at 10:49
The send question may be a company policy security issue https://stackoverflow.com/questions/17883088/sending-mail-using-outlook-where-the-send-method-fails. — niton, May 12 '23 at 10:49
Comment out the `.Display` and keep only `.Send`. As for the attachment, what type and size is it? — Kostas K., May 12 '23 at 11:09

score 0 · Answer 1 · answered May 12 '23 at 16:04

The Attachments.Add method creates a new attachment in the Attachments collection. The source of the attachment can be a file (represented by the full file system path with a file name) or an Outlook item that constitutes the attachment.

You need to copy the file from the shared/network folder to any local folder and then use a local file path for attaching a file. The Attachments.Add is not designed to deal with file shares or any other URLs.

Unable to send Bulk emails through excel VBA

1 Answers1