using excel to email using outlook, need help on sending from account(s) portion

[SteveM99]

Can anyone help me with some excel vba code to send emails from excel. I already have some rough code but I have two email addresses I like to send from. I prefer to have the send from email address in the excel file so i don't need to code it all the time. I tried using Ron Bruin's site but not sure that is current with my version of excel or outlook. Can anyone help me with some basic code. Below is what I have which works except for the send from part. I tried using the send on behalf option and later tried the sendusingaccount code but I honestly don't know this type of detail coding.

Sub Send_Email_With_Attachment()


'set up using email program
Dim outlookApp As Object
Dim outlookMail As Object


'Set up the recipients
Dim recipient1 As String
recipient1 = Worksheets("4Q-20").Range("b17")


'Set up the recipients
Dim CC1 As String
CC1 = Worksheets("4Q-20").Range("b18")


'set up the sender (optional)
Dim frm1 As String
frm1 = Worksheets("4Q-20").Range("b20")


'set up the subject
Dim subject As String
subject = Worksheets("4Q-20").Range("b19")


'Set up the email body message
Dim strbody As String
strbody = Worksheets("4Q-20").Range("b21")


'Set up file locations and name by referencing them from 4Q-20
Dim file1 As String
file1 = Worksheets("4Q-20").Range("b15")


Dim file2 As String
file2 = Worksheets("4Q-20").Range("b16")


'----------------------------------------------


Set outlookApp = CreateObject("outlook.application")
Set outlookMail = outlookApp.createitem(0)


With outlookMail


.To = recipient1
.CC = CC1
'.subject = "Line of Credits Update"
.subject = subject
.BodyFormat = 2
.HTMLBody = strbody
'.Attachments.Add file1
'.Attachments.Add file2
.sentOnbehalfofName = frm1
.Display
'.Send


End With


Set outlookMail = Nothing
Set outlookApp = Nothing




End Sub

[Logit]

.

See if this helps :


Resource : http://www.learnexcelmacro.com/wp/2011/12/how-to-send-an-email-using-excel-macro-from-gmail-or-yahoo/

[SteveM99]

Not really. Was looking for using outlook not yahoo or gmail.

[gmayor]

You should use code from Ron's site to start Outlook (see link in the code below) and it is better not to use variable names that can be confused with commands.


Option Explicit

Sub Send_Email_With_Attachment()
'Graham Mayor - https://www.gmayor.com - Last updated - 23 Jan 2020
'Use the code from the folllowing link to start Outlook
'http://www.rondebruin.nl/win/s1/outlook/openclose.htm

'set up using email program
Dim olApp As Object
Dim outlookMail As Object
Dim olAccount As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object
Dim strRecipient1 As String
Dim CC1 As String
Dim frm1 As String
Dim strSubject As String
Dim strBody As String
Dim strFile1 As String
Dim strFile2 As String
Dim strAccount As String

'identify the account by display name
strAccount = "account name"

'Set up the recipients
strRecipient1 = Worksheets("4Q-20").Range("b17")

'Set up the recipients
CC1 = Worksheets("4Q-20").Range("b18")

'set up the sender (optional)
frm1 = Worksheets("4Q-20").Range("b20")

'set up the subject
strSubject = Worksheets("4Q-20").Range("b19")

'Set up the email body message
strBody = Worksheets("4Q-20").Range("b21")

'Set up file locations and name by referencing them from 4Q-20
strFile1 = Worksheets("4Q-20").Range("b15")
strFile2 = Worksheets("4Q-20").Range("b16")

'----------------------------------------------

Set olApp = outlookApp()

For Each olAccount In olApp.Session.Accounts
If olAccount.DisplayName = strAccount Then
Set outlookMail = olApp.createitem(0)
With outlookMail
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor

Set .SendUsingAccount = olAccount

.Display 'required!
.To = strRecipient1
.CC = CC1
.subject = strSubject
.BodyFormat = 2
Set oRng = wdDoc.Range
oRng.collapse 1
oRng.Text = strBody
'.Attachments.Add strfile1
'.Attachments.Add strfile2
'.sentOnbehalfofName = frm1
'.Send
End With
Exit For
End If
Next olAccount

Set outlookMail = Nothing
Set olApp = Nothing
Set olAccount = Nothing
Set olInsp = Nothing
Set wdDoc = Nothing
Set oRng = Nothing

End Sub

If you are going to add attachments from a worksheet, you need to include code to establish the file(s) exist before attaching them e.g. by calling

Private Function FileExists(strFullName As String) As Boolean
'Graham Mayor
'strFullName is the name with path of the file to check
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(strFullName) Then
FileExists = True
Else
FileExists = False
End If
lbl_Exit:
Set FSO = Nothing
Exit Function
End Function

[SteveM99]

Graham, I will be testing over next day or so. Thank you very much for this. I am such a newbie but not afraid to try!!!