Allowing user to select send mailbox when emailing from excel

[pyrrhicvictori]

Hi all, I must admit I am not even sure I am describing this with all of the correct terms- so please forgive me in advance...

I have a workbook that uses buttons to send various emails.
This workbook is used by multiple users- the users have multiple email boxes tied to the accounts they are working on.
In Outlook we use a drop down to select the mailbox we want to send the email from (using the .display email code)

When sending the emails from excel (using the button) it disables our ability to select the email box we want to send from. This is causing a lot of problems since we cannot send an email intended for one account from an email box of another account without violating some NDAs (the email boxes are labeled by client org).

I have seen some solutions that work if I am always the user (I can input my email boxes into the code and select) BUT I cannot do this for multiple users and all of their email boxes for various reasons:
Confidentiality of accounts- users should not see each others accounts
I simply do not know all the mailboxes I would even have to list

Is there a way to stop Outlook from disabling the mailbox select when sending from excel?

The message we get when the email opens and we try to select the correct mailbox is : You do not have permissions to send the message on behalf of the specified user.

An example of code I have for one of the emails is: (I have some that send attachments, some that insert an image into the body of the email, etc)
Private Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi," & vbNewLine & vbNewLine & _
'Various info goes here depending on email need
"Thank you," & vbNewLine & _
Application.UserName

On Error Resume Next

With OutMail
.To = Worksheets("Home").Range("C26") & ";" & Worksheets("Home").Range("C30") & ";" & Worksheets("Home").Range("C34") & ";" & Worksheets("Home").Range("I8") & ";" & Worksheets("Home").Range("C38")
.cc = Worksheets("Home").Range("C42") & ";" & Worksheets("Home").Range("C46")
.BCC = ""
.Subject = "Link to EPS for " & Worksheets("Home").Range("C4") & " " & Worksheets("Home").Range("C7")
.Body = strbody
'.Attachments.Add (various attachments might be added depending on the button they choose)
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

End Sub


Thanks for help or ideas in advance!

pv

 

[paulxmw]

Hi I'm not too sure exactly what you need but you could add the following code to your code

'                                       Test = sheet name      I = location of email address
.SentOnBehalfOfName = Sheets("Text").Range("I1:I5").Value
.BCC = ""
        .Subject = "Link to EPS for " & Worksheets("Home").Range("C4") & " " & Worksheets("Home").Range("C7")
        .Body = strbody
        '.Attachments.Add (various attachments might be added depending on the button they choose)
        .Display   
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
[\code]

I tested this with a dropdown list and it picked up the various boxes i named in it no problem

Paul

 

[paulxmw]

Hi
Did this work for you

Paulxmw

 

[pyrrhicvictori]

Hi Paul,
I have been tied up with other stuff but will be attempting this Monday and will let you know! Sorry for my delayed response!
Jess

 

[pyrrhicvictori]

hi Paul,
YOU ARE AMAZING! thank you so much. Yes this worked. I simply added the following line to my email options in the code:

.SentOnBehalfOfName = Sheets("Sheet name").Range("cell value").Value '<----- I already have them entering their own contact information, so this just refers to the cell that they already enter their own personal email address in.

Where this is really awesome is that I don't have to have a pre made list of potential email address (and then manage that list over time). Once the user hits the button to send the email, it automatically opens an email in Outlook that is sent from their own default personal email address, BUT allows a drop down option to let them enter an alternate email address to send from. This is more than I could ask for. Thank you!

 

[paulxmw]

Hi
Your more then welcome, im glad i can pass on some of the knowledge i have learned on this and other forums

Paul