Consulting

Results 1 to 5 of 5

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

  1. #1
    VBAX Regular
    Joined
    Dec 2015
    Posts
    46
    Location

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

    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

  2. #2
    VBAX Mentor Logit's Avatar
    Joined
    Sep 2016
    Posts
    452
    Location

  3. #3
    VBAX Regular
    Joined
    Dec 2015
    Posts
    46
    Location
    Not really. Was looking for using outlook not yahoo or gmail.

  4. #4
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    VBAX Regular
    Joined
    Dec 2015
    Posts
    46
    Location
    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!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •