• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

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
0
hindersaliva
Asked:
hindersaliva
  • 2
  • 2
2 Solutions
 
NorieCommented:
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
 
NorieCommented:
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
 
Rgonzo1971Commented:
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now