Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Smile Email Signature

    Hi all, I am new to this site, I have been trying to add an email signature to and email body via VBA, the following is what i have so far, all works and opens a new email but still doesn't add the orders@ signature, any help would be appreciated.

    In my Public Subs module is the following

    Function GetBoiler(ByVal sFile As String) As String

    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close

    Command Button [Event Procedure]

    Dim MyApp As Outlook.Application
    Dim MyItem As Outlook.MailItem
    Dim BodyMessage, Orders, SigString, Signature As String

    SigString = Environ("appdata") & "\Microsoft\Signatures\orders@.htm"
    MsgBox (SigString)
    If Dir(SigString) <> "" Then
    Signature = GetBoiler(SigString)
    Else
    MsgBox ("There Is No Orders Signature To add")
    End If

    Set MyApp = CreateObject("Outlook.application")
    Set MyItem = MyApp.CreateItem(olMailItem)
    BodyMessage = "THIS IS A TEST EMAIL FROM ME"



    With MyItem

    .To = "dave@dmoses.co.uk"
    .Subject = "TEST MAIL FROM ME"
    .Body = BodyMessage & Chr(10) & Chr(10) & Signature
    .Display
    End With

    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi June, thank you for your help, I have the following code that creates the email and adds signature but the logo (bmp) is there but says the picture can't be displayed, any ideas ?

    Dim MyApp As Outlook.Application
    Dim MyItem As Outlook.MailItem
    Dim BodyMessage, Orders, SigString, Signature As String


    SigString = Environ("appdata") & "\Microsoft\Signatures\orders@.htm"
    'MsgBox (SigString)
    If Dir(SigString) <> "" Then
    Signature = GetBoiler(SigString)
    Else
    MsgBox ("There Is No Orders Signature To add")
    End If

    Set MyApp = CreateObject("Outlook.application")
    Set MyItem = MyApp.CreateItem(olMailItem)
    BodyMessage = "THIS IS A TEST EMAIL FROM DM"

    With MyItem
    .To = "dave@dmoses.co.uk"
    .Subject = "TEST MAIL FROM DM"
    .Body = BodyMessage & vbNewLine & vbNewLine & Signature
    Debug.Print BodyMessage
    .HTMLBody = BodyMessage & vbNewLine & vbNewLine & Signature
    Debug.Print .HTMLBody
    .Display
    End With

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The signature file code seems rather convoluted.
    Does it work if you just reference a standard image file e.g. Bmp?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Okay, finally had some time to find code buried in my db. This approach depends on an email opening with default Outlook email signature. Opens email and concatenates message with the original HTMLBody and overwrites the body. My signature has a png image. Tested and works. Cannot get the GetBoiler function to handle image. I'm not the only one, research indicates it just won't work at all. Seems the fso method alters the image link path. I examined the HTML code from both methods and there are definite differences.

    Code:
    Private Sub EmailWithSig()
    Dim oApp As Object, OMail As Object
    Set oApp = CreateObject("Outlook.application")
    Set OMail = oApp.CreateItem(0)
    With OMail
        .To = "Address"
        .Subject = "Subject"
        .Display 'email must be displayed for the next line to work
        .HTMLBody = "Message." & vbNewLine & .HTMLBody
         '.Send
    End With
    Set OMail = Nothing
    Set oApp = Nothing
    End Sub
    Last edited by June7; 05-23-2018 at 03:59 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    hi ridders, it seems to add the embedded logo but says it can't be displayed, I am not sure how to embed the image taken from the word doc located where my SigString is located, it certainly adds the square embedded and address and contact details but not the image!

    How do you mean by referencing the image file ??, I am guessing to copy the bmp or jpg to a local folder and follow the path in vba then debug.print for example "mylogo" from the location ?, I think it's so close now but I haven't a great experience with integrating images with access! thank you for your help throughout

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If you want an image that is in Word doc, need to save image to external location then use HTML img tags to embed image in email HTMLBody. Build your own signature string in the VBA procedure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by DMT Dave View Post
    hi ridders, it seems to add the embedded logo but says it can't be displayed, I am not sure how to embed the image taken from the word doc located where my SigString is located, it certainly adds the square embedded and address and contact details but not the image!

    How do you mean by referencing the image file ??, I am guessing to copy the bmp or jpg to a local folder and follow the path in vba then debug.print for example "mylogo" from the location ?, I think it's so close now but I haven't a great experience with integrating images with access! thank you for your help throughout
    Apologies - didn't see this earlier

    This is an example of HTML email code:
    The image part has been highlighted in RED - replace with your own file path but same notation

    i haven't included the SendHTMLEMailCDO function used to send the email as that's not relevant here

    Code:
    Sub TestHTMLEMail()
    
    'setup email info
    aTo = strSDAManagerEMail
    'aCC = strSDAEMailSupport
    aFrom = strSendUserName
    
    aSubject = "Test HTML Email message - no attachments"
        aHTMLBody = "This is a <FONT size=5><FONT color=#800000><B>TEST message</B><FONT size=3>.<FONT color=#000000>" & _
        " to check <I>HTML email</I> with no attachments from the <B><I>" & GetProgramName() & "</B></I> program" & _
        " <P><IMG border=0 hspace=0 alt='' src='file://G:/Programs/MendipDataSystems/CommonFiles/SDA/Images/SDAUpdater1.gif' align=baseline></P>"
    
    'send the email using CDO email code
    SendHTMLEMailCDO aTo, aCC, aSubject, aHTMLBody, aFrom, aPath
    
    End Sub
    and this is the email sent:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	63 
Size:	27.4 KB 
ID:	34176

    HTH
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi, I am still not getting the signature to add! it's now not adding the signature! I added your line highlighted red but unsure if I still have this incorrect ??

    Dim MyApp As OutLook.Application
    Dim MyItem As OutLook.MailItem
    Dim BodyMessage, Orders, SigString, Signature As String
    Dim MySig As String
    MySig = "C:\Users\David\Desktop\DMT\EmailLogo.Jpg" 'added
    If Left(Me.txtCustomer, 3) = "Thy" Then
    ' SigString = Environ("appdata") & "\Microsoft\Signatures\orders@.htm"
    SigString = "<P><IMG border=0 hspace=0 alt='' src='file:C:\Users\David\AppData\Microsoft\Signatu res\orders@.htm' align=baseline></P>"
    End If
    If Left(Me.txtCustomer, 3) = "Del" Then
    SigString = Environ("appdata") & "\Microsoft\Signatures\dispatch@.htm"
    End If

    'MsgBox (SigString)
    'If Dir(SigString) <> "" Then
    ' Signature = GetBoiler(SigString)
    'Else
    ' MsgBox ("There Is No Orders Signature To add")
    'End If
    Set MyApp = CreateObject("Outlook.application")
    Set MyItem = MyApp.CreateItem(olMailItem)
    BodyMessage = "THIS IS A TEST EMAIL FROM DM"
    With MyItem
    .To = "dave@dmoses.co.uk"
    .Subject = "TEST MAIL FROM DM"
    Debug.Print BodyMessage
    .HTMLBody = BodyMessage & Chr(10) & Chr(10) & SigString
    Debug.Print .HTMLBody
    .Display
    End With

  10. #10
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    just noticed a space in the path "signatu res, this is a copy-paste issue, NO spaces in the vba !!! regards

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    if you use code tags by clicking the # button in the toolbar, it makes code easier to read and fixes the phantom 'spa ce' error. just paste or type your code between the [c o d e] and [/c o d e] placeholders. NOTE spaces added deliberately to fool the forum software

    Not sure why still not working for you.
    Does it work in the Debug.Print .HTMLBody line?
    Are you definitely sending as HTML and not plain email?
    Try formatting text as I did to check

    Also I wonder whether having @ as part of the file name could be an issue for email.
    Test with an image file which has no special characters in the name
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi ridders, could you kindly explain more about sending as html and not plain email, this part maybe i am not understanding how to, based on my code, is this sending as HTML ??

    The test with image file, again i have never worked with email sending images, does my code look as though it should work ??

    Is it possible to highlight the above and re paste code ??, ignore the space in the word signature as there is no space in the code!!

    Once again, much appreciate your help

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by DMT Dave View Post
    Hi ridders, could you kindly explain more about sending as html and not plain email, this part maybe i am not understanding how to, based on my code, is this sending as HTML ??

    The test with image file, again i have never worked with email sending images, does my code look as though it should work ??

    Is it possible to highlight the above and re paste code ??, ignore the space in the word signature as there is no space in the code!!

    Once again, much appreciate your help
    I can't tell how it will be sent as you haven't included the code used to send the email

    You didn't answer my questions - did you do what I asked?
    Yes you can add code tags retrospectively - it will display exactly as the original
    If you do this by pasting the original code, you keep any code indentation & avoid the unwanted 'spa ce'

    Code:
    Dim MyApp As OutLook.Application
    Dim MyItem As OutLook.MailItem
    Dim BodyMessage, Orders, SigString, Signature As String
    Dim MySig As String
    MySig = "C:\Users\David\Desktop\DMT\EmailLogo.Jpg" 'added
    If Left(Me.txtCustomer, 3) = "Thy" Then
    ' SigString = Environ("appdata") & "\Microsoft\Signatures\orders@.htm"
    SigString = "<P><IMG border=0 hspace=0 alt='' src='file:C:\Users\David\AppData\Microsoft\Signatures\orders@.htm' align=baseline></P>"
    End If
    If Left(Me.txtCustomer, 3) = "Del" Then
    SigString = Environ("appdata") & "\Microsoft\Signatures\dispatch@.htm"
    End If
    
    'MsgBox (SigString)
    'If Dir(SigString) <> "" Then
    ' Signature = GetBoiler(SigString)
    'Else
    ' MsgBox ("There Is No Orders Signature To add")
    'End If
    Set MyApp = CreateObject("Outlook.application")
    Set MyItem = MyApp.CreateItem(olMailItem)
    BodyMessage = "THIS IS A TEST EMAIL FROM DM"
    With MyItem
    .To = "dave@dmoses.co.uk"
    .Subject = "TEST MAIL FROM DM"
    Debug.Print BodyMessage
    .HTMLBody = BodyMessage & Chr(10) & Chr(10) & SigString
    Debug.Print .HTMLBody
    .Display
    End With
    EDIT:
    Sorry - I thought you were importing an actual signature as an image file
    However I've just read the code properly & looked at the equivalent path to the signatures .htm file on my PC.
    Now I understand what it is you are trying to include, that method won't work
    My code was for importing images NOT htm files and it won't work for .htm

    You could include .htm as an attachment but I'm not sure how to include it in the body of your mail
    There may be a direct way but I can't think what it is offhand

    So if you only have a few signatures to include, I suggest you do one of the following:
    a) save a screenshot of each signature as an image file then use my code to include that the the email body
    b) save the signatures as html text in your code and include that
    c) save the signatures to a table then use a variable or function based on a DLookup to reference them in the code
    d) save the signature as 'business cards' and include those
    Last edited by isladogs; 05-27-2018 at 05:41 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Click image for larger version. 

Name:	Email Result.jpg 
Views:	55 
Size:	123.5 KB 
ID:	34221Hi ridders52, I have finally got somewhere with this logo adding to email body, still need a bit of advice, I have now got the logo to add, problem 1, it adds before BodyMessage and it is not aligning to the left, it is probably 2 tabs from the left, any idea how to fix ?? I will send updated code and a snippet of email very close now though , I can probably add the signature text to a Public Sub and set criteria's from there I guess

    Highlighted is how I got a logo to mail body, aligning and adding after BodyMessage is now the required fix

    Dim MyApp As OutLook.Application
    Dim MyItem As OutLook.MailItem
    Dim BodyMessage, Orders, SigString, Signature As String
    Dim MySig As String

    If Left(Me.txtCustomer, 3) = "Thy" Then
    SigString = Environ("appdata") & "\Microsoft\Signatures\orders@.htm"
    End If
    If Left(Me.txtCustomer, 3) = "Del" Then
    SigString = Environ("appdata") & "\Microsoft\Signatures\dispatch@.htm"
    End If
    Set MyApp = CreateObject("Outlook.application")
    Set MyItem = MyApp.CreateItem(olMailItem)
    BodyMessage = "THIS IS A TEST EMAIL FROM DM" & Chr(10) & Chr(10) & _
    "<img src=C:\Users\David\Desktop\DMT\EmailLogo.jpg" & Chr(10) & Chr(10) & _
    SigString
    ' "<html><p>This Is a Picture.</p>" & _
    ' "<img src=C:\Users\David\Desktop\DMT\EmailLogo.jpg"

    With MyItem
    Signature = MyItem.Body
    .To = "dave@dmoses.co.uk"
    .Subject = "TEST MAIL FROM DM"
    Debug.Print BodyMessage
    MyItem.HTMLBody = BodyMessage & Chr(10) & Chr(10) & Signature
    Debug.Print .HTMLBody
    .Display
    End With

    Email snippet

    Click image for larger version. 

Name:	Email Result.jpg 
Views:	55 
Size:	123.5 KB 
ID:	34221

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Progress....
    If you go back to my code, note my use of 'align baseline' to place the image under the text.
    There are other alignment options but it's several years since I did this and would need to check.
    However the W3Schools site includes a very good section on using HTML
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Gmail signature with email address syntax error
    By Gina Maylone in forum Access
    Replies: 2
    Last Post: 01-26-2016, 07:41 AM
  2. Add Outlook Signature to Email - CODE
    By floyd in forum Programming
    Replies: 1
    Last Post: 11-27-2013, 09:23 AM
  3. Code to include Outlook Signature in Email from Access
    By floyd in forum Code Repository
    Replies: 0
    Last Post: 11-27-2013, 08:52 AM
  4. Replies: 7
    Last Post: 11-27-2013, 08:37 AM
  5. adding signature to the email sendobject
    By webisti in forum Access
    Replies: 3
    Last Post: 07-10-2012, 03:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums