proper code for sending emails?

jerry12302

Active Member
Joined
Apr 18, 2005
Messages
432
Can someone look at this code and tell me if it is appropriate? Is there a better way to do it?

I have been having problems with emails getting through when I use this.

Sub MailerTest()
Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.createitem(olmailitem)
With objmail
.To = "someemailaddress@yahoo.com"
.cc = "ccsomeemailaddress@hotmail.com"
.Subject = "Test 12/9 10:39"
.Body = "This is a Test 12/9 10:39"
.NoAging = True
.Attachments.Add "C:\somefile.xls"
.display
End With
Set objmail = Nothing
Set objol = Nothing
SendKeys "%{s}", True
End Sub

Thanks for your help!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,343
Office Version
  1. 365
Platform
  1. Windows
That code looks fine to me.

What problems are you having?

Is it something to do with a warning from Excel/Outlook?
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
That's pretty similar to code I'm using. Only difference is that once I knew it was working well, I commented out the .Display and put in .Send which I assume is what your trying to do with SendKeys???

{edit}If you are sending to more than one recipient, you'd want to make the TO string a semi-colon delimited list or else add to the RECIPIENTs collection.{/edit}
 

jerry12302

Active Member
Joined
Apr 18, 2005
Messages
432
Thanks for replying!

Norie, yes, I am getting an error in Outlook:

Your message did not reach some or all of the intended recipients.

Subject: December, 2005 NNAG File Test
Sent: 12/9/2005 10:19 AM

The following recipient(s) could not be reached:

'jerry12302@yahoo.com' on 12/9/2005 10:19 AM
554 TRANSACTION FAILED

Ever seen this?

Greg, I copied the code off the internet, I don't understand all of it myself, if I remove .display and replace it with .send, then I don't need the sendkeys line?

I actually modified this to include a loop through a list of email recipients and the file attachments they each need, I brought it back to this level to keep things simpler. This code itself doesn't even work anymore.

I shouldn't say it doesn't work at all, the emails are in fact "sent", they are in my sent folder, but I get this error back in my inbox right away saying they are undeliverable. I've run tests and the recipients are not getting the emails.

Thanks!

Thanks for your help!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,343
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How are you getting that error?

I don't think that's a problem with the code.

Sounds to me as though the message didn't reach it's destination, perhaps because of an incorrect email address.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
Comment out the Sendkeys line and leave in the .Display call. This will show you the e-mails that are being created. Are they addressed correctly?
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022

ADVERTISEMENT

Also, are you running this from Excel? Or Outlook? I assumed Outlook given the command in the SendKeys call. But if you didn't write it yourself, now I'm wondering...
 

jerry12302

Active Member
Joined
Apr 18, 2005
Messages
432
Thanks,

I tested the code by using my own email addresses, as well as some coworkers' addresses. They were addressed properly. They were never received.

I am running the macro from Excel. It worked fine the first few times I tested it.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
And putting in .Send (inside the With objMail structure) didn't work?
 

jerry12302

Active Member
Joined
Apr 18, 2005
Messages
432
Greg,

I tried putting the .send in the code, instead of .display (and commented out the sendkeys line), and I still get the same error message in Outlook.

They appear in my sent box like a normal email that was sent, but I get it returned in my inbox immediately with the errors noted.

I can't send emails manually from Outlook anymore either, I've tried several tests and I get the same result.

I think the code is fine, but somehow Outlook has been affected.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,440
Messages
5,831,643
Members
430,079
Latest member
lucasabreueng

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
Top