VBA opening Outlook email from Excel

[bjornltd]

Hi

I am very new to VBA and need some help figuring this out. Greatly appreciate any help. I have searched and tried to change some solutions to my needs, but I’m coming up short…

Case:

By pressing a button, in Excel, the user of the workbook (there are several users), should open a new Outlook email with:

• Email sent on behalf of:
  • If cell B42:
    • States “A”, send from “#1” email account
    • States “B”, send from “#2” email account
    • States “C”, send from “#3” email account
• To: Value in cell B37
• Subject: “Fixed text” + value of cell B44
• Signature in HTML from the current user. The signatures of the users don’t have the same filename.

All the information is in the same Excel sheet.

Sorry if this has been answered before, just could not find a solution that suits my needs. Thanks for any help

 

[Logit]

Here is an excellent resource :

Mail from Excel with Outlook (VBA)

 

[bjornltd]

Hi,

I have actually looked at this, but my skillset in VBA is really bad! I really don’t understand how to get it right!
I tried some different solutions, but either I get an error message or nothing happens…..

 

[MSKO]

Try this:

Dim OlApp As Object
Dim NewMail As Object
Dim selectedemail As String

Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)

Select Case Cells.Range("B42").Value
Case "A"
selectedemail = "#1@email.com"
Case "B"
selectedemail = "#2@email.com"
Case "C"
selectedemail = "#3@email.com"

End Select
With NewMail
.To = selectedemail
.Subject = "Fixed Text " & Cells.Range("B42").Value
.display
End With
Set NewMail = Nothing
Set OlApp = Nothing