Sending e-mail through VBA (.Send issue)

[ScatmanKyle]

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.

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

 

[shknbk2]

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

Dim mail As Object

to

Dim mail As MailItem

 

[ScatmanKyle]

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]

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!