+ Reply to Thread
Results 1 to 11 of 11

Trying to add a default signature into an email from VBA

Hybrid View

  1. #1
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Trying to add a default signature into an email from VBA

    If the answer to the above is yes, then this should work:

    Sub Mail_workbook_Outlook_1()
        Dim OutApp As Object
        Dim OutMail As Outlook.MailItem
        Dim signature As String
        
        EmailTo = Worksheets("E-mail Sheet").Range("B26")
        
        Dim Found As Range
        
        EmailTo = Worksheets("E-mail Sheet").Range("B26")
        
        Set Found = Sheets("Current Clients").Columns("C").Find(What:=Sheets("E-mail Sheet").Range("A26").Value, _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                                                   
        If Not Found Is Nothing Then
            If IsEmpty(Found.Offset(, -2)) = False Then
                If MsgBox(Range("A26").Value & vbNewLine & vbNewLine & "An item request has  been sent on: " & Found.Offset(, -2).Value & vbNewLine & _
                "Items were recieved on: " & Found.Offset(, -1).Value & vbNewLine & vbNewLine & "See Current Clients page for more information" & vbNewLine & vbNewLine _
                & "Do you want to continue?", vbYesNo) = vbNo Then
                    Exit Sub ' stop macro if user selects No
                End If
            End If
        End If
    
        
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        On Error Resume Next
        With OutMail
            .display
            signature = .htmlBody
            .To = Worksheets("E-mail Sheet").Range("B26")
            .CC = Worksheets("E-mail Sheet").Range("B19")
            .BCC = ""
            .Subject = "Updating Credit File - " & Worksheets("E-mail Sheet").Range("D26")
            .htmlBody = "Dear " & Worksheets("E-mail Sheet").Range("C26") & "," & "<br><br>" & _
            "We are requesting the following information to bring your credit file up to date." & "<br><br>" & Worksheets("E-mail Sheet").Range("B2") _
            & Worksheets("E-mail Sheet").Range("C2") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B3") & Worksheets("E-mail Sheet").Range("C3") _
            & "<br><br>" & Worksheets("E-mail Sheet").Range("B4") & Worksheets("E-mail Sheet").Range("C4") & "<br><br>" & Worksheets("E-mail Sheet").Range("B5") _
            & Worksheets("E-mail Sheet").Range("C5") & "<br><br>" & Worksheets("E-mail Sheet").Range("B6") _
            & Worksheets("E-mail Sheet").Range("C6") & "<br><br>" & Worksheets("E-mail Sheet").Range("B7") _
            & Worksheets("E-mail Sheet").Range("C7") & "<br><br>" & Worksheets("E-mail Sheet").Range("B8") _
            & Worksheets("E-mail Sheet").Range("C8") & "<br><br>" & Worksheets("E-mail Sheet").Range("B9") _
            & Worksheets("E-mail Sheet").Range("C9") & "<br><br>" & Worksheets("E-mail Sheet").Range("B10") _
            & Worksheets("E-mail Sheet").Range("C10") & "<br><br>" & Worksheets("E-mail Sheet").Range("B11") _
            & Worksheets("E-mail Sheet").Range("C11") & "<br><br>" & Worksheets("E-mail Sheet").Range("B12") _
            & Worksheets("E-mail Sheet").Range("C12") & "<br><br>" & Worksheets("E-mail Sheet").Range("B13") _
            & Worksheets("E-mail Sheet").Range("C13") & "<br><br>" & Worksheets("E-mail Sheet").Range("B14") _
            & Worksheets("E-mail Sheet").Range("C14") & "<br><br>" & Worksheets("E-mail Sheet").Range("B15") _
            & Worksheets("E-mail Sheet").Range("C15") & "<br><br>" & Worksheets("E-mail Sheet").Range("B16") _
            & Worksheets("E-mail Sheet").Range("C16") & "<br><br>" & Worksheets("E-mail Sheet").Range("B17") _
            & Worksheets("E-mail Sheet").Range("C17") & "<br><br>" & "If possible please provide us this information by " & Worksheets("E-mail Sheet").Range("A2") _
            & "<br><br>" & "If you have any questions please contact " & Worksheets("E-mail Sheet").Range("A19") & "at " & Worksheets("E-mail Sheet").Range("C19") _
            & "<br><br>" & "Sincerely," & "<br><br>" & signature
            .display
            .SendUsingAccount = OutApp.Session.Accounts.Item(2)
    
        
        End With
        On Error GoTo 0
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  2. #2
    Registered User
    Join Date
    06-26-2015
    Location
    St.Paul, Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Trying to add a default signature into an email from VBA

    Arkadi, thanks for the reply!
    I should have specified that the signature is a default for my 2nd email account, but it's not the default signature for the default email account. The code I have here sends the email from the 2nd Outlook account, so I figured it would pull that default signature with your code, however it is not. Any suggestions? Thanks again!
    Last edited by murp5972; 07-10-2015 at 10:38 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] default email signature
    By bigfishprf in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-08-2015, 10:55 AM
  2. [SOLVED] Excel VBA code for saving PDF to file & adding default signature to Email
    By bdouglas1011 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-02-2014, 01:54 AM
  3. generating automatic email with default signature and hyperlink
    By jerrydiaz in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 04-24-2014, 11:55 AM
  4. send selected range in email with default outlook email signature included
    By mdsickler in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-04-2013, 10:50 PM
  5. Unable to link default email signature to email
    By paxile2k in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-15-2010, 06:52 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1