Outlook signature not working on some computers (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 07:01
Joined
Sep 21, 2011
Messages
13,964
Hi all,

I have posted this question in the MS Office forum

http://www.msofficeforums.com/outlook/35942-outlook-signature-template-excel-macro.html

but have not had any response.

I am hoping someone here might have come across this as and have a solution as I know I lot of you send emails from Access via Outlook.
Admittedly the source is Excel this time, but the process is the same?

My question was

I created a macro in an excel workbook that will take data from that workbook and send an email using an email template stored on a network drive and the path added to trusted locations.
All fine, works OK on my PC and a colleague's. However the person who would normally use it, no signature is attached to the email when displayed?

I've had a look at her settings and the only thing I could see when I go to Programmatic Access is that it states no antivirus found, yet AVG is on the PC.
I've installed the latest version just in case and now it says valid.

When she creates an email herself, signature works fine. If she uses a template that she opens from extra menu options on a user added toolbar, the signature works fine.

I've got rid of the warning and the email template opens now as normal, just with the normal signature missing

I tested on another computer and get the same result, but cannot see what the differences might be. The second computer that does not have the signature does not have any warnings.
Where would I start looking for anomalies please? Does anyone have any ideas?
 
Last edited:

Minty

AWF VIP
Local time
Today, 07:01
Joined
Jul 26, 2013
Messages
10,346
Don't suppose you could post the macro code?
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:01
Joined
Sep 21, 2011
Messages
13,964
Of couse Minty,

It is nothing special though.

Code:
Sub Send_Emails()

Dim strLastCell As String, strTemplatePath As String, strTemplate As String, strHTML As String
Dim dtCalledDate As Date
Dim strClient As String, strEmail As String
Dim lLastRow As Long, iColon As Integer
Dim blnDisplayMsg As Boolean
Dim rngCell As Range

' Now the Outlook variables
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim strSigPath As String, strSignature As String, strAttachFile As String
Dim intBody As Integer

On Error GoTo Err_Handler
' Send immediately or display first
If Range("O1").Value = "Send" Then
    blnDisplayMsg = False
Else
    blnDisplayMsg = True
End If

'Establish all the static Outlook Data

    ' Get appdata path
    'strAppdata = Environ("Appdata")
    
    ' Set paths
    strTemplatePath = "\\SRVPH\JAGPH\Torch\Templates\"
    strTemplate = "Welcome TWM.oft"
    'strSigPath = strAppdata & "\Microsoft\Signatures\Ssafa.htm"
    
    ' Create the Outlook session.
    'Set objOutlook = GetObject(, "Outlook.Application")
    'Set objOutlook = New Outlook.Application
    ' This code *should* open outlook if not running or just use it if it is
    Set objOutlook = OutlookApp()


'Select correct workbook and sheet
'Workbooks("Email merge.xlsm").Activate
Sheets("Clients").Select

lLastRow = Range("B" & Rows.Count).End(xlUp).Row
strLastCell = "M" & lLastRow

' First set the filter to get just the rows we want
With ActiveSheet
    .AutoFilterMode = False
    .Range("A1:" & strLastCell).AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="="
End With

'For Each rngCell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
For Each rngCell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
        Range(rngCell.Address).Cells.Activate
        dtCalledDate = ActiveCell.Offset(0, 9)
        strClient = Trim(ActiveCell.Value) & " " & Trim(ActiveCell.Offset(0, 2).Value)
        strEmail = ActiveCell.Offset(0, 11)
        
        ' Now populate the outlook fields
        ' Create the message.
        'Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
        
        Set objOutlookMsg = objOutlook.CreateItemFromTemplate(strTemplatePath & strTemplate)
    
        With objOutlookMsg
            ' Add the To recipient(s) to the message.
            Set objOutlookRecip = .Recipients.Add(strEmail)
            objOutlookRecip.Type = olTo
    
            ' Now add variables
            'strHTML = .HTMLBody
            .HTMLBody = Replace(.HTMLBody, "InsertName", strClient)
            .HTMLBody = Replace(.HTMLBody, "InsertCalled", Format(dtCalledDate, "dd mmmm yyyy"))
            '.HTMLBody = strHTML
            
            ' Add the CC recipient(s) to the message.
            'Set objOutlookRecip = .Recipients.Add("** SSAFA West Glamorgan Branch")
            'objOutlookRecip.Type = olCC
            
            ' Should we display the message before sending?
            If blnDisplayMsg Then
                .Display
            Else
                .Save
                .Send
            End If
        End With

    ' Update the Mailed column
    ActiveCell.Offset(0, -1).Value = Date

Next rngCell

' Switch off the filter
ActiveSheet.AutoFilterMode = False
ActiveWorkbook.Save

' Tidy up
Set rngCell = Nothing

Proc_Exit:
    Exit Sub
    
Err_Handler:
    MsgBox Err.Number & " " & Err.Description
    Resume Proc_Exit



End Sub
Also some code from the Net as if Outlook was not running I get an Activex error message.
However the signature was not working before I added this extra code.
Code:
Option Explicit
#Const LateBind = True

Const olMinimized As Long = 1
Const olMaximized As Long = 2
Const olFolderInbox As Long = 6

#If LateBind Then

Public Function OutlookApp( _
    Optional WindowState As Long = olMinimized, _
    Optional ReleaseIt As Boolean = False _
    ) As Object
    Static o As Object
#Else
Public Function OutlookApp( _
    Optional WindowState As Outlook.OlWindowState = olMinimized, _
    Optional ReleaseIt As Boolean _
) As Outlook.Application
    Static o As Outlook.Application
#End If
On Error GoTo ErrHandler
 
    Select Case True
        Case o Is Nothing, Len(o.Name) = 0
            Set o = GetObject(, "Outlook.Application")
            If o.Explorers.Count = 0 Then
InitOutlook:
                'Open inbox to prevent errors with security prompts
                o.Session.GetDefaultFolder(olFolderInbox).Display
                o.ActiveExplorer.WindowState = WindowState
            End If
        Case ReleaseIt
            Set o = Nothing
    End Select
    Set OutlookApp = o
 
ExitProc:
    Exit Function
ErrHandler:
    Select Case Err.Number
        Case -2147352567
            'User cancelled setup, silently exit
            Set o = Nothing
        Case 429, 462
            Set o = GetOutlookApp()
            If o Is Nothing Then
                Err.Raise 429, "OutlookApp", "Outlook Application does not appear to be installed."
            Else
                Resume InitOutlook
            End If
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Unexpected error"
    End Select
    Resume ExitProc
    Resume
End Function

#If LateBind Then
Private Function GetOutlookApp() As Object
#Else
Private Function GetOutlookApp() As Outlook.Application
#End If
On Error GoTo ErrHandler
    
    Set GetOutlookApp = CreateObject("Outlook.Application")
    
ExitProc:
    Exit Function
ErrHandler:
    Select Case Err.Number
        Case Else
            'Do not raise any errors
            Set GetOutlookApp = Nothing
    End Select
    Resume ExitProc
    Resume
End Function
Don't suppose you could post the macro code?
 

Minty

AWF VIP
Local time
Today, 07:01
Joined
Jul 26, 2013
Messages
10,346
You're using early binding in the outlook references, so it could simply be that.
/probably grandma eggs/
If you have different versions of excel, outlook and/or access between the machines, you have to use late binding.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:01
Joined
Sep 21, 2011
Messages
13,964
Hi Minty,
We all use Office 2007.
Would you be able to point me on how to try it with late Binding to see if that is the cause?

Edit:
I would still like to know about the late binding, but you have hit the nail on the head.
Both the non signature computers did not have SP3 applied. I have just applied it to one computer and now the signature is there. ! :D

Thank you Minty !!
 

Minty

AWF VIP
Local time
Today, 07:01
Joined
Jul 26, 2013
Messages
10,346
There are quite a lot of descriptions out there but this one seems the most straightforward; http://sourcedaddy.com/ms-access/late-binding.html

Basically rather than directly declaring a specific object type, instead you define an object then assign a object type to it. This prevents Access or any other VBA project trying to reference the object with the same library as the machine the code was written on.

Whilst this isn't a problem if you develop on 2007 but everyone runs 2013 versions it is a problem the other way around. And as you discovered a SP version change can be enough to trigger the issue, as the reference library may well have been updated.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:01
Joined
Sep 21, 2011
Messages
13,964
Thank you Minty,

I'll check that link out and have a bit of a play.
At least now I know the first place to check even of we all use 2007.
 

Users who are viewing this thread

Top Bottom