Excel VBA - programatically adding reference to Outlook 2010

I've seen how to do that here
http://stackoverflow.com/questions/24630378/i-need-to-code-for-the-correct-reference-to-ms-outlook-from-excel

I need to make my Excel application determine which version of Outlook to change the Reference to, via Workbook Open.

When I run it I get a 'not trusted' message. I am distributing the Excel application to non-technical users. Is it possible to have that reference added without an intervening message? If so how?

Thanks
hindersalivaAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Why not use late-binding instead?

Then no reference wil be required.
0
 
hindersalivaAuthor Commented:
Norie, yes. I've never done that before. I want to try it. Shall look up.
0
 
NorieVBA ExpertCommented:
If you post your code, or even just part of it, here I'm sure we can give you some help/pointers.
0
 
hindersalivaAuthor Commented:
Thanks Norie.

Sub CreateAndSendFromTemplate(strNameKnownAs As String, strInductionDate As String, strTemplateName As String)

    'https://msdn.microsoft.com/en-us/library/aa220081(v=office.11).aspx
    'and Ron de Bruin
    
    Dim myOlApp As Outlook.Application
    Dim oMail As Outlook.MailItem
    Set myOlApp = CreateObject("Outlook.Application")
    
    strTemplateFolder = Range("EmailTemplatesFolder").Value  '"C:\MyCode\Sending emails\"
    strPath = strTemplateFolder + strTemplateName
    
    Set oMail = myOlApp.CreateItemFromTemplate(strPath)
    
    With oMail
        strFind = "FieldInductionDate"
        strNew = strInductionDate
        .HTMLBody = Replace(oMail.HTMLBody, strFind, strNew)
    End With
    
    With oMail
        strFind = "FieldKnownAs"
        strNew = strNameKnownAs
        .To = "hiran@desilva.com"
        .HTMLBody = Replace(oMail.HTMLBody, strFind, strNew)
        .Send
    
    End With

End Sub

Open in new window

0
 
Rgonzo1971Connect With a Mentor Commented:
Hi,
pls try

Sub CreateAndSendFromTemplate(strNameKnownAs As String, strInductionDate As String, strTemplateName As String)

    'https://msdn.microsoft.com/en-us/library/aa220081(v=office.11).aspx
    'and Ron de Bruin
    
    Dim myOlApp As Object
    Dim oMail As Object
    Set myOlApp = CreateObject("Outlook.Application")
    
    strTemplateFolder = Range("EmailTemplatesFolder").Value  '"C:\MyCode\Sending emails\"
    strPath = strTemplateFolder + strTemplateName
    
    Set oMail = myOlApp.CreateItemFromTemplate(strPath)
    
    With oMail
        strFind = "FieldInductionDate"
        strNew = strInductionDate
        .HTMLBody = Replace(oMail.HTMLBody, strFind, strNew)
    End With
    
    With oMail
        strFind = "FieldKnownAs"
        strNew = strNameKnownAs
        .To = "hiran@desilva.com"
        .HTMLBody = Replace(oMail.HTMLBody, strFind, strNew)
        .Send
    
    End With

   Set oMail = Nothing 
   Set myOlApp = Nothing

End Sub

Open in new window

Just replace the Dims with "Object" since you are already using late binding for the OL Object

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.