Change 'FROM' when sending email

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,922
Office Version
  1. 365
Platform
  1. Windows
I have a couple of accounts in Outlook, and I have been trying to change which account emails are being sent from when creating the emails in Access. I found a way, but it only seems to work with the early binding method.

Code:
Sub CreateEmail(rName As String, Brass As String, Agents As String)Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim tMail As Outlook.MailItem
Dim objFolder As Outlook.MAPIFolder
Dim fName As String
Dim acct As Outlook.Account


Set olApp = New Outlook.Application
Set objns = Outlook.GetNamespace("MAPI")
Set objInbox = objns.GetDefaultFolder(olFolderInbox)
Set objFolder = objns.Folders("xxxx@cbp.dhs.gov")
Set tMail = Outlook.CreateItem(olMailItem)
fName = Environ("UserProfile") & "\Desktop\" & "Past Due Vehicles.pdf"


For Each a In olApp.Session.Accounts
    If a.SmtpAddress = "xxxx@cbp.dhs.gov" Then
        Set acct = a
        Exit For
    End If
Next a


DoCmd.OutputTo acOutputReport, rName, acFormatPDF, fName, False


With tMail
    .SendUsingAccount = acct
    .Subject = "Vehicles Out Past Due Report"
    .Body = "You are receiving this email because you have a vehicle that has been checked out too long." & vbCrLf & "Vehicles Must be turned in at the end of every shift." & vbCrLf & vbCrLf & "See Attachment."
    .Attachments.Add fName
    .Display
End With


Kill fName


End Sub

That does work, but this is going to be used by people on different versions of Access, so I have to use late binding. I used the same method as above, but it still creates the email from my primary account and not the account I'm going for. I've debugged it and made sure that the account variable, acct, is the correct account.

Code:
Sub CreateEmailLB(rName As String, Brass As String, Agents As String)Dim olApp As Object
Dim tMail As Object 'Outlook.MailItem
Dim objFolder As Object 'Outlook.MAPIFolder
Dim acct As Object
Dim fName As String


Set olApp = CreateObject("Outlook.Application")
Set objns = olApp.GetNamespace("MAPI")


Set objInbox = objns.GetDefaultFolder(olFolderInbox)
Set objFolder = objns.Folders("xxxx@cbp.dhs.gov")
Set tMail = olApp.CreateItem(olMailItem)


For Each a In olApp.Session.Accounts
    If a.SmtpAddress = "xxxx@cbp.dhs.gov" Then
        Set acct = a
        Exit For
    End If
Next a


fName = Environ("UserProfile") & "\Desktop\" & "Past Due Vehicles.pdf"


DoCmd.OutputTo acOutputReport, rName, acFormatPDF, fName, False


With tMail
    .SendUsingAccount = acct
    .Recipients.Add Agents
    .CC = Brass
    .Subject = "Vehicles Out Past Due Report"
    .Body = "You are receiving this email because you have a vehicle that has been checked out too long." & vbCrLf & "Vehicles Must be turned in at the end of every shift." & vbCrLf & vbCrLf & "See Attachment."
    .Attachments.Add fName
    .Display
End With


Kill fName


End Sub

Any idea as to why this might be and how to fix it?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
don't even bother doing that

it will just be marked as spam when it arrives in the person's email
it may not even be delivered at all -- some email services will just block emails that have their "from" altered

besides, the user can always go into the their email settings and set it to "view headers" which will show the real from
 
Upvote 0
Although you didn't specify, I assume your "testing" when it doesn't work is on the other machines, not your own.
It seems that you might have this issue:
Code:
For Each a In olApp.Session.Accounts
    If a.SmtpAddress = "xxxx@cbp.dhs.gov" Then
        Set acct = a
        Exit For
    End If
Next a

if there is an account with smtp address of "xxxx@cbp.dhs.gov" on *your* machine then well and good - it is using that account. If the account does not exist on *other* machines then no such luck - nothing will be set here for your object variable acct.

Other than that, I can think of no reason why late binding would not work and early binding would work (except maybe possibly there are multiple outlooks to bind to ??)
 
Upvote 0
Thanks for the replies. Turned out to be a pretty simple fix.

This
Code:
[COLOR=#333333].SendUsingAccount = acct[/COLOR]

needed to be written like this...
Code:
Set .SendUsingAccount = acct

Still unclear why this wasn't an issue when using early binding, but whatever. It's working now so I'm happy.

Thanks again for taking a look at it.
 
Upvote 0
this is what i use every day and should at least point you in the wright direction

With OutMail
.SentOnBehalfOfName = "email.address@yourcouncil.gov.uk"
.to = Cws.Cells(Rnum, 1).Value
.Subject = "Urgent action required - invoice in query"
.HTMLBody = StrBody & RangetoHTML(rng) & sBody 'changed
.DeferredDeliveryTime = Delay
.Display 'Or use Send
End With
On Error GoTo 0

Set OutMail = Nothing
End If
 
Last edited:
Upvote 0
Not that I can tell which library is being defaulted to when using late binding in your case, but it can happen that the syntax for a library that you declare can be different from one that you don't. Letting Access decide on the library can also create a situation where you use methods of one version in development that works fine on your pc but don't exist in someone else's earlier version. I would not have spotted the fix, so I learned something; thanks for posting the fix.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top