proper code for sending emails?

jerry12302

Active Member
Joined
Apr 18, 2005
Messages
407
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!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
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,014
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
407
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,061
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,014
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,014

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
407
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,014
And putting in .Send (inside the With objMail structure) didn't work?
 

jerry12302

Active Member
Joined
Apr 18, 2005
Messages
407
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,819
Messages
5,574,503
Members
412,599
Latest member
Schu94
Top