Solved

Excel VBA - programatically adding reference to Outlook 2010

Posted on 2016-11-01
5
42 Views
Last Modified: 2016-11-04
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
Comment
Question by:hindersaliva
  • 2
  • 2
5 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 250 total points
ID: 41868133
Why not use late-binding instead?

Then no reference wil be required.
0
 

Author Comment

by:hindersaliva
ID: 41868140
Norie, yes. I've never done that before. I want to try it. Shall look up.
0
 
LVL 33

Expert Comment

by:Norie
ID: 41868143
If you post your code, or even just part of it, here I'm sure we can give you some help/pointers.
0
 

Author Comment

by:hindersaliva
ID: 41868164
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
 
LVL 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 41868180
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you don't know how to downgrade, my instructions below should be helpful.
Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now