Consulting

Results 1 to 3 of 3

Thread: Email signature disappears when clipboard contents are added

  1. #1

    Email signature disappears when clipboard contents are added

    Hi all, I'm a total novice so looking for a bit of help....

    I've got a button within an Excel sheet that basically generates an email with some text in the body and the contents of the clipboard (this is always from using the snipping tool). I just can't seem to get the picture to display below the first couple of lines of text within the email and also, my signature always disappears:

    Private Sub Alta1_Click()


    Dim OutApp As Object
    Dim OutMail As Object
    Dim olInsp As Object
    Dim wdDoc As Object
    Dim oRng As Object


    On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then Set OutApp = CreateObject("Outlook.Application")
    On Error Resume Next

    Set OutMail = OutApp.CreateItem(0)

    With OutMail
    .BodyFormat = 2
    .To = ""
    .CC = ""
    .Subject = "Altahullion 1 fault"
    If Time < TimeValue("12:00:00") Then
    .Body = "Good Morning," & vbNewLine & vbNewLine & _
    "Please see the fault below:"
    ElseIf Time > TimeValue("12:00:00") And Time < TimeValue("17:00:00") Then
    .Body = "Good Afternoon," & vbNewLine & vbNewLine & _
    "Please see the fault below:"
    Else
    .Body = "Good Evening," & vbNewLine & vbNewLine & _
    "Please see the fault below:"


    End If

    On Error Resume Next



    Set olInsp = .GetInspector
    Set wdDoc = olInsp.WordEditor
    Set oRng = wdDoc.Range

    oRng.collapse 0
    oRng.Paste
    .Display
    End With

    Any assistance would be greatly appreciated.
    Thanks,
    James.

  2. #2
    There are a few provisos to editing the Outlook message body and the first is not to write to the .Body as that wipes out the signature you want to keep. You also need to open Outlook correctly, for which the function identified and linked at the top of the following macro is ideal. Then you can create a message with your signature pasted content and text

    Private Sub Alta1_Click()
    'Graham Mayor - https://www.gmayor.com - Last updated - 05 Feb 2019
    'Requires the code from
    'http://www.rondebruin.nl/win/s1/outlook/openclose.htm
    'to start Outlook correctly
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim olInsp As Object
    Dim wdDoc As Object
    Dim oRng As Object
    
        On Error Resume Next
        Set OutApp = OutlookApp()
    
        Set OutMail = OutApp.CreateItem(0)
    
        With OutMail
            .BodyFormat = 2
            .To = ""
            .CC = ""
            .Subject = "Altahullion 1 fault"
    
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set oRng = wdDoc.Range(0, 0)
    
    
            If Time < TimeValue("12:00:00") Then
                oRng.Text = "Good Morning," & vbCr & vbCr & _
                            "Please see the fault below:"
            ElseIf Time > TimeValue("12:00:00") And Time < TimeValue("17:00:00") Then
                oRng.Text = "Good Afternoon," & vbCr & vbCr & _
                            "Please see the fault below:" & vbCr & vbCr
            Else
                oRng.Text = "Good Evening," & vbNewLine & vbNewLine & _
                            "Please see the fault below:" & vbCr & vbCr
            End If
            oRng.collapse 0
            oRng.Paste
            .Display
        End With
        Set OutApp = Nothing
        Set OutMail = Nothing
        Set olInsp = Nothing
        Set wdDoc = Nothing
        Set oRng = Nothing
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    That's great. Thank you very much for your help. Much appreciated

Posting Permissions

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