Automatically Adding VBA Object Library - VBAProject Reference

Question

I created a program for myself and a few of my colleagues. One of the functions of my program runs a macro that grabs data from the Excel sheet and opens a populated email in Outlook.

In order to run this macro you need to add the Object Library "Microsoft Outlook 16.0 Object Library".

Is there any way to do this automatically, so that all of my colleagues can run this macro?

Here's the code for my email generator.

' -- Drafts an email in Outlook -- '
Public Sub emailDraft()
Dim objOutlook As Outlook.Application
Dim objMail As Outlook.MailItem
Dim masterWS As Worksheet
Dim masterWB As Workbook
Dim counter As Long

Set objOutlook = Outlook.Application
Set objMail = objOutlook.CreateItem(olMailItem)

Set masterWB = Workbooks("Master.xlsm")
Set masterWS = masterWB.Worksheets("MASTER SHEET")

objMail.To = masterWS.Range("F6").Value
objMail.CC = "[email protected]"
objMail.Subject = masterWS.Range("F7").Value
objMail.Body = masterWS.Range("F8").Value

objMail.Display
End Sub

You can also use late binding and skip the references altogether. — Warcupine
– Warcupine, Commented Feb 6, 2020 at 16:24
@Warcupine The link you sent is good, but still requires me to manually add a reference in order to run the code — Nikolajs
– Nikolajs, Commented Feb 6, 2020 at 16:29

Nikolajs · Accepted Answer · 2020-02-06 16:51:09Z

2

Thank you @Warcupine for your insight.

I was able to solve this by using late binding. My updated code is below.

' -- Drafts an email in Outlook -- '
Public Sub emailDraft()
Dim objOutlook As Object    'Outlook.Application
Dim objMail As Object       'Outlook.MailItem
Dim masterWS As Worksheet
Dim masterWB As Workbook
Dim counter As Long

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(olMailItem)

'objOutlook.CreateItem (olMailItem)

Set wb = ThisWorkbook
Set wsMAST = wb.Worksheets("MASTER")

objMail.To = wsMAST.Range("K2").Value
objMail.CC = "[email protected]"
objMail.Subject = wsMAST.Range("k3").Value
objMail.Body = wsMAST.Range("k4").Value

objMail.Display
End Sub

answered Feb 6, 2020 at 16:51

Nikolajs

3351 gold badge4 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Automatically Adding VBA Object Library - VBAProject Reference

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related