Sending e-mail through VBA (.Send issue)

ScatmanKyle

Board Regular
Joined
Oct 26, 2015
Messages
61
Office Version
  1. 365
Platform
  1. Windows
Hello!

I'm currently testing around with sending e-mails through a VBA procedure in Excel. I'm having one minor inconvenience and one actual issue.

The minor issue is the options such as the ".to", ".subject" etc aren't popping up in a preview window or automatically capitalizing when moving lines, so I don't know what options are available or if I'm entering the correct code.

The major issue is I can't send the e-mail. With the below code, I was able to add .Display and confirm that an e-mail with the correct information is drafted, but I can't figure out how to send it. I've seen a .Send command on guides, but it's not working for me.

VBA Code:
Sub Email_Send()

Dim outlookApp As Object
Dim mail As Object


Set outlookApp = CreateObject("Outlook.Application")
Set mail = outlookApp.createitem(0)

With mail
.To = "myemail@email.com"
.Subject = "Test"
.Body = "This is a test"
.Send
End With

End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
270
Office Version
  1. 365
Platform
  1. Windows
Other than changing the To line to my own email address, your code works perfectly for me. Email sent with success automatically. When you display the email, is it using the right From address? Can you then send it normally using the Send button?

Regarding the minor issue, it is a problem of late-binding vs early binding. Lots of info on the web about that. You're using late-binding with 'mail' being dimmed an Object. To use early-binding and thus see the menu after you type 'mail.', you can add a reference to the Outlook library: in the Tools->References menu of the editor, locate "Microsoft Outlook ##.0 Object Library" and select it. The ## will depend on your version of Office: mine is 16.0 since I'm using Office 365.

Then change
VBA Code:
Dim mail As Object
to
VBA Code:
Dim mail As MailItem
 

ScatmanKyle

Board Regular
Joined
Oct 26, 2015
Messages
61
Office Version
  1. 365
Platform
  1. Windows
When you say "Selected", do you just mean it should have the checkmark beside it? I'm using 365 and the 16.0 had a checkmark by default.

My vba isn't reading MailItem as being a thing, so it didn't work when I changed the code.

I tried manually sending from the .Display and can confirm it does send properly from there.
 

ScatmanKyle

Board Regular
Joined
Oct 26, 2015
Messages
61
Office Version
  1. 365
Platform
  1. Windows
Just did some more searching and found out it's an issue with outlook's security features and I can't change it without going to my IT department. Thanks shknbk2!
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

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