I need to send message from the certain E-mail address (E-mail Distribution Group) with VBA code. I have permission "Send as" and I can do it by hands, changing field "From".
Use SentOnBehalfOfName, and as long as your Exchange account has SendAs permission for the shared mailbox or distribution group, it will be sent from the shared account or group, not sent on behalf of.
If you want to send from another account in your Account list, you'll use SendUsingAccount and select the account by index or by name. See Macros to send message using the default account or a specific account for code samples.
SendAs an address code Sample
Note: this macro is used with Exchange account to send from addresses you have SendAs permissions.
Sub CustomMailMessage()
Dim OutApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Recipient
Dim Recipients As Recipients
Set OutApp = CreateObject("Outlook.Application")
Set objOutlookMsg = OutApp.CreateItem(olMailItem)
Set Recipients = objOutlookMsg.Recipients
Set objOutlookRecip = Recipients.Add("alias@domain.com")
objOutlookRecip.Type = 1
objOutlookMsg.SentOnBehalfOfName = "sales@domain.com"
objOutlookMsg.Subject = "Testing this macro"
objOutlookMsg.HTMLBody = "Testing this macro" & vbCrLf & vbCrLf
'Resolve each Recipient's name.
For Each objOutlookRecip In objOutlookMsg.Recipients
objOutlookRecip.Resolve
Next
'objOutlookMsg.Send
objOutlookMsg.Display
Set OutApp = Nothing
End Sub
This works perfectly. Thanks!
I have two columns in my excel file. One for "from" email address , and other for "to" email address. I want to send email by picking one email id from the "from" column and send it to one of the "to" email address. I am able to pick up the "from" email address in a variable but not able to send email from it. Outlook sends email from "my" email address. Please Help.
what type of email account do you use? If exchange, is the From address hosted in exchange and do you have send as rights to it?
I use gmail account. all the email ids are gmail accounts.
Hi Diane, I am using the 'SentOnBehalfOfName' function in VBA for Access - most current version and using Office 365 - outlook.
the issue is that for every email to send i have a screen pop up saying
Microsoft Access
send to ....'the email address'
OK
is there code i can add so i don't have to click 'OK' for each email to send ?
That is the outlook security dialog? If you have a valid antivirus, you shouldn't get that dialog, but there are reg keys you can set to avoid it. See https://www.slipstick.com/developer/change-programmatic-access-options/
Hello Diane,
Could you please help me? I am not a macro master, but I found few different codes and mixed them. Everything works great, but I would like to send calendar invitation from some specific email, that is not my default email address. I tried to use 'SentOnBehalfOfName' function, or Set olSubCal = olSubCalA.Folders(Calendar).Folders("specific name") and other options using 'shared calendar' that I found, but non of this works.
This is code I have, how could I add the address from which I could send invitations? Lets say, mail would be: test@specific.com
(...)
Dim olApp As Outlook.Application
Dim olAppt As Outlook.AppointmentItem
Dim blnCreated As Boolean
Dim olNs As Outlook.Namespace
Dim CalFolder As Outlook.MAPIFolder
Dim i As Long
On Error Resume Next
Set olApp = Outlook.Application
If olApp Is Nothing Then
Set olApp = Outlook.Application
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0
Set olNs = olApp.GetNamespace("MAPI")
Set CalFolder = olNs.GetDefaultFolder(olFolderCalendar)
Set olAppt = CalFolder.Items.Add(olAppointmentItem)
With olAppt
.MeetingStatus = olMeeting
.Subject = Cells(1, 3)
(...)
Please advise.
Thank you,
Diana
Hi Diane,
Your VBA resources has overall been extremely helpful. I have also studied your VBA for sending out Meeting Request via Excel. I am currently stuck as I am trying to send the meeting request through a generic departmental account that I have permission to send as using the following code but I continuously run into an ERROR. I have not had much luck with any other resources available online. Is it because we are not able to set the origin of the interview request to anyone else but ourself? Your assistance would be greatly appreciated!
I tried utilising the following in my Interview Scheduling VBA without much luck:
.SendUsingAccount = "me@msn.com".SendUsingAccount = "me@msn.com"Any advice is greatly appreciated.
if the account is not in your profile, you need to use SentOnBehalfOfName. It needs to be set before the message is displayed.
Diane,
thank you for quick response! This is additional mailbox I have assigned to my outlook so i have 2 mailboxes and 2 calendars so in list of my calendars I have both - mine and this additional one. It is not visible as 'shared calendar', but as normal calendar.
Do you know how to add it so it could work?
What references are needed in VBA to send email in Access 2016. my code worked in 2013, but now error loading DLL. runtime error '48', Error in loading DLL. Help anyone?? (my last post same issue, I miss typed my email)
Are you using 32 or 64bit Office? Did you use the same bitness in Outlook 2013? Where is the dll located on the hard drive? If you are referencing outlook or access, are you referencing the correct version in the VBA editor's tools, references?
Hi Diane,
Is it not possible to use the property "From" instead of "SentOnBehalfOfName" ?
Thanks a lot,
Christophe
No, that field is used for recipients, not sending accounts.
When sending emails from VBA, is there any way to check programmatically if email failed to get through (say, could not connect to Exchange server, or any other reason...)?
AFAIK, no.