Sending emails question

[Abraxus]

I have a database used frequently to send email to large numbers of people.

In my outlook (this is for work) I have the ability to change the FROM so it comes from another email address than mine.

Can I have my VBA code select that FROM automatically? Right now I have to display the email, change it manually, then hit send...

Here's my code:

Code: Select all

Sub SendMail()
    Debug.Print Now
    'Create a new Microsoft Outlook session
    Set appOutlook = CreateObject("outlook.application")
    
    'Open the list to send to
    Dim db As dao.Database
    Set db = CurrentDb
    Dim rst As dao.Recordset
    Set rst = db.OpenRecordset("SELECT * FROM tblAddresses WHERE Sent = FALSE", dbOpenDynaset)
    While Not rst.EOF
        Debug.Print rst!Address
        
        'create a new message
        Set Message = appOutlook.CreateItem(olMailItem)
                 
        With Message
           .Subject = "This is the subject"
    
           Dim strBody As String
           strBody = "This is the body<br><br><br>"
           strBody = strBody & "And More body<br><br>"
                          
           .HTMLBody = strBody
                
           .to = rst!Address
           '.Cc = strCC
           
           '.Attachments.Add ("C:\Temp\ShippingReport - " & strCostCenter & ".xls")
           .Display
           .Send
        End With
                
        'Update as sent
        rst.Edit
        rst!Sent = True
        rst.Update
        rst.MoveNext
    Wend
    
    'appOutlook = Nothing
    
    Debug.Print Now
    MsgBox "Done!"
End Sub

I've tried

Code: Select all

.From = "otheraddress@test.com"

to no avail...

Any hep is appreciated

[Wendell]

I believe what you want to change is the .Sender property - see https://msdn.microsoft.com/en-us/librar ... 69056.aspx for the details. Note that it does require the address you set to be a defined account in your Outlook installation. Hope this helps.

[Abraxus]

I tried adding .sender = "Other Email Address" and I tried .sender = "Other Email Account" but neither worked...

I also tried .addressentry.Sender = "other@emailaddress.com"

I don't read anything in your link that speaks to setting the value, just reading the value...still confused.

It's not a huge deal, as I rarely have to do this sort of thing, just a nice to have, if I could get it working...

[Mark L]

I think you need to use: .SendUsingAccount= xxx 'where xxx = Outlook Account

[HansV]

Ron de Bruin provides details about using SendUsingAccount: Use the mail account you want in your mail macro.

[Abraxus]

Thanks everyone!

What ended up working is this:

Code: Select all

.SentOnBehalfOfName = """TheAccountName"" <TheEmail@theCompany.com>"

[HansV]

Good to hear that this worked for you.

Be aware that this doesn't really change the sender. The recipient will see something like "Abraxus on behalf of TheAccountName" as sender.

[Abraxus]

Good to hear that this worked for you.

Be aware that this doesn't really change the sender. The recipient will see something like "Abraxus on behalf of TheAccountName" as sender.

I thought that might happen, but in my tests you can not tell that it was not sent by the other account. The headers look like it originated from that account. possibly because i have my outlook set up to allow me to send form that account?

Either way, I'm good and appreciate everyone's advice!

[HansV]

Indeed, it's probably because you have permission to send from that account.