[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel VBA - programatically adding reference to Outlook 2010

Posted on 2016-11-01
5
Medium Priority
?
90 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 35

Accepted Solution

by:
Norie earned 1000 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 35

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 53

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

656 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