Sending Emails using VBA

[jerry.rozario]

Hello,
I have been using the code below to send emails to different individuals. However, since last week, all emails have been bouncing back. Although the "send" button shows that the email has been sent, I receive undeliverable messages. Additionally, an error message pops up when I try to select a different email address from the dropdown menu after sending one email. The only solution is to close the workbook and reopen it, which is annoying and time-consuming. I wish I can attached the entire workbook along with the error messages, If there is a way to attach the entire workbook for your testing please let me know. Please help me to solve this issue.

Sub Send_Email_With_Snapshot()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Email")

Dim lr As Long
lr = sh.Range("A" & sh.Rows.Count).End(xlUp).Row

sh.Range("A1:G1" & lr).Select

With Selection.Parent.MailEnvelope.Item
        .To = sh.Range("I6").Value
        .Subject = sh.Range("I7").Value
        '.Attachments.Add ""
        .Send
        

End With

MsgBox "Email Sent"

End Sub

 

[mumps]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Micron]

You can't upload files here. Some responders will access files that are saved to a drop box/One Drive; some won't. Regardless, the problem I think I see with that is anyone doing that gains your email list on your sheet, and in order to test code, would be sending emails to those people, no?

I suggest that you always post the error number, its message content and flag which line raises the error, otherwise you've given nothing to provide any clue.

 

[jerry.rozario]

Thank you for the suggestions. Here is the error code I received after sending one email. The first image shows the email I attempted to send which bounced. Then, I selected another recipient from the Name dropdown and tried to send the email again, but received an error message.

 

[Micron]

If an email bounces (what does that mean to you, exactly?) that's an issue with the mail sending side of things - address is wrong, server permissions, etc.
Unfortunately the error message is too vague to be of any help. However, I suspect your code is receiving a failure signal from the email server but Excel doesn't have a corresponding message that it can choose to display so you get that one.

Can you send email to these people using the addresses listed in your sheet and by pasting the address into your mail app's recipient (To) field? If not you probably need to fix that. If you can, then all I can think of is that you post your file - but anyone helping would have to alter the address to something else so that these people don't start getting test messages. By doing that, the parameters change, which could make testing pointless, so you need to verify that you can manually send to these people first.