Sending Emails using VBA

jerry.rozario

New Member
Joined
Dec 22, 2010
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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.


VBA Code:
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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).
 
Upvote 0
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.
 
Upvote 0
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.
 

Attachments

  • EmailSentBoxMsg.JPG
    EmailSentBoxMsg.JPG
    65.9 KB · Views: 26
  • ErrorAfterSentOneEmail.JPG
    ErrorAfterSentOneEmail.JPG
    69.2 KB · Views: 27
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top