?
Solved

Excel VBA - programatically adding reference to Outlook 2010

Posted on 2016-11-01
5
Medium Priority
?
81 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 34

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 34

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 52

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Suggested Courses

771 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