SentOnBehalfOfName issue when using excel to create an outlook email

[TomBurton]

I am a self taught vba user with not a high level. Most of my code is used by finding related code and editing it for my own use.

I have been using some vba code which uses SentOnBehalfOfName. One day it randomly stopped working. When the macro is run, it loads the email and then outlook closes down (no code to do that) outlook then re-opens and there is no draft email to use. If that step of code is removed, then the email loads fine and the user has to manually change the sent from to the required email. All users have permissions to send from the shared email. Another weird thing is that if outlook is closed before the macro is run, then the email is created and outlook doesn't crash. Anyone got any suggestions as to why this happens or what I can do to stop it happening?

Sub Create_Email()
    
    On Error GoTo ErrHandler
    
    ' SET Outlook APPLICATION OBJECT.
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
    Application.ScreenUpdating = False
    Sheets("Email Information").Visible = True
    Application.CalculateFull
    ' CREATE EMAIL OBJECT.
    Dim objEmail As Object
    Set objEmail = objOutlook.CreateItem(olMailItem)

    With objEmail
        .to = "Staff1@company.com; Staff2@company.com etc"
        .sentOnbehalfofName = "sharedemail@company.com"  <----- this is the step that is causing the issue
        .cc = "Staff3@company.com; Staff4@company.com etc"
        .Subject = "Results on Report"
        .Body = "Put all information in here based on the results in report"
        'Add the report as an attachment
        .Attachments.Add ("File location")
        .Display        ' Display the message in Outlook.
    End With
    
    ' CLEAR.
    Set objEmail = Nothing:    Set objOutlook = Nothing
        
ErrHandler:
    '
    Sheets("Email Information").Visible = False
    Application.ScreenUpdating = True
End Sub

Kind regards,
Tom

 

[Gasman]

No idea as to why TBH.
I used to send on a particular account. Do you have that option?

 

[TomBurton]

Hi Gasman,

I'm unsure of what you mean. Each user has their own account but the email wants sending using a shared email.

 

[Gasman]

Hi Gasman,

I'm unsure of what you mean. Each user has their own account but the email wants sending using a shared email.

Yes, and for that there must be a different account surely?
I have several email accounts on my Outlook (admittedly only a personal Outlook), but chose which to send with when volunteering with SSAFA

           ' Add the To recipient(s) to the message. (Also work out which account to send on 12/07/19)
            If rs!ClientDivision = "SSW" Then
                Set objOutlookRecip = .Recipients.Add("Jim Needs - Personal")
                objOutlookRecip.Type = olTo
                intAccount = 2
            Else
                Set objOutlookRecip = .Recipients.Add("South West Wales SSAFA")
                objOutlookRecip.Type = olTo
                intAccount = 3
            End If

........
        With objOutlookMsg           
               .SendUsingAccount = objOutlook.Session.Accounts.Item(intAccount)

 

[TomBurton]

There is a different account, a shared one which our IT department gives access to required staff. We can manually select the email we want to send from once the email has opened but I want to automate that step as it should always come from the shared email account when sending this email.

Can you talk through your code a bit more? I don't follow it and therefore don't see how it'll help me. I am relatively new to this so sorry about that.

 

[Gasman]

See the code snippet I posted in this thread.
Uncomment the the debug.print to see all the accounts, then use to find the relevant account and use as I posted previously.

This is not a solution, I am just offering a workaround.
As I was working on my own computer, I knew that the order would not change, so reverted to using the actual index values, 2 or 3.
You would need to obtain that from checking the name in case IT added another account, etc.

Edit: It would help if I posted the link

Open Msg File using a specific Outlook Account

Hey everyone :D I created a system that automatically links emails from outlook to orders by storing the emails as a .msg file on our server. You can then open the msg file again by pressing a button in a form. To open the Msg File i am using the following code: Public Declare PtrSafe...

www.access-programmers.co.uk

 

[TomBurton]

Sorry to be a pain. I don't know what you mean. I see your code that you posted but I'm unsure what I'm doing with it. Can you talk me through what I need to do as if I had no clue of VBA?

Thanks for your help.

 

[Gasman]

Run that code from the link I posted and post back what accounts you and your colleagues have on your computer.
I see I never included it initially , now it is there in post #6 in this thread.

That function allows you to pass the name or email address (you decide which and uncomment/comment accordingly) and it returns the index of the account, and you use that as I posted in the posted code in this thread.

 

[TomBurton]

Hi Gasman,
Been busy so hadn't managed to get round to looking at this in a while. I ran the code (I think correctly) and it returned 1 account. This may be why I can't access the 2nd account (shared account) email. I can send from the shared email when I have an email open and can manually change it from my own email to the shared one by clicking on the from dropdown. Why can I change it and access it here but not using the macro?

 

[Gasman]

Use that ListEmailAccounts function to display all your accounts. Uncomment the debug.print as it states. Then you know which name to pass to it. Give a dummy name to AcctToUse so that it goes through all accounts.
Then when you know the name use that in the function.

It has been a while since I created it, but it worked for me, but then as it was personal, I just hardcoded the index number.
The name would have to be the same on every computer, else you would need to do.more work.