Adding a reference ,using it and remove the reference at the end programmatically using VBA

 

 

 

Most common Office GUID:

Microsoft Excel	{00020813-0000-0000-C000-000000000046}
Microsoft Word	{00020905-0000-0000-C000-000000000046}
Microsoft PowerPoint	{91493440-5A91-11CF-8700-00AA0060263B}
Microsoft Access	{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}
Microsoft Outlook	{00062FFF-0000-0000-C000-000000000046}

 

 

 

 

 

 

 

' ----------------------------------------------------------------
' Purpose: Add Microsoft Outlook Object Library, call a procedure using that library, then remove Outlook Object Library
' ----------------------------------------------------------------
Sub callingProcedureMSOutlookObjLibrary()

    Dim strGUID As String

    'Microsoft Outlook GUID
    strGUID = "{00062FFF-0000-0000-C000-000000000046}"

    'Check if reference is already added to the project, if not add it
    If F_isReferenceAdded(strGUID) = False Then
        ThisWorkbook.VBProject.REFERENCES.AddFromGuid strGUID, 0, 0
    End If
    
    'Calling the procedure using Outlook object library
    Call procedureUsingMSOutlookObjectLibrary
    
    'Check if reference is added to the project, if yes remove
    If F_isReferenceAdded(strGUID) = True Then
        ThisWorkbook.VBProject.REFERENCES.Remove F_idReferenceByGUID(strGUID)
    End If
    
End Sub
' ----------------------------------------------------------------
' Purpose: Create new Outlook document with early binding, add two paragraphs, align the 2nd one to center
' ----------------------------------------------------------------

'sending mail with pdf attachments


Sub procedureUsingMSOutlookObjectLibrary()


Dim olapp As Outlook.Application
Dim olemail As Outlook.MailItem
Dim objFSO As Object
Dim objFldr As Object
Dim objFile  As Object
Dim strfullpath As String
Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFldr = objFSO.GetFolder(Environ("UserProfile") & "\Desktop\excel_to_pdf_2")

Set olapp = New Outlook.Application
Set olemail = olapp.CreateItem(olMailItem)

With olemail

.BodyFormat = olFormatHTML

.Display
.HTMLBody = "Dear Someone" & "<br>" & .HTMLBody
'.Attachments.Add Environ("UserProfile") & "\Desktop\excel_to_pdf_2\Financial Sample.Canada.20200920_022807.pdf"
For Each objFile In objFldr.Files
        strfullpath = objFldr.Path & "\" & objFile.Name

        If LCase(Trim(objFSO.GetExtensionName(strfullpath))) = "pdf" Then
        'MsgBox strfullpath
            'SendasAttachment (strfullpath)
          .Attachments.Add strfullpath
        End If
    Next

.To = "allsourav2@gmail.com"
.Subject = "Test using VBA"
.Send




End With
Set olapp = Nothing
Set olemail = Nothing
End Sub

' ----------------------------------------------------------------
' Purpose: Check if an Object Library refernce is added to a VBAProject or not
' ----------------------------------------------------------------
Function F_isReferenceAdded(referenceGUID As String) As Boolean

    Dim varRef As Variant

    'Loop through VBProject references if input GUID found return TRUE otherwise FALSE
    For Each varRef In ThisWorkbook.VBProject.REFERENCES
        
        If varRef.GUID = referenceGUID Then
            F_isReferenceAdded = True
            Exit For
        End If
        
    Next varRef

End Function
' ----------------------------------------------------------------
' Purpose: Return Object Library reference as object, found by its GUID
' ----------------------------------------------------------------
Function F_idReferenceByGUID(referenceGUID As String) As Object

    Dim varRef As Object

    For Each varRef In ThisWorkbook.VBProject.REFERENCES
        
        If varRef.GUID = referenceGUID Then
            Set F_idReferenceByGUID = varRef
            Exit For
        End If
        
    Next varRef

End Function

Source:https://www.excelcise.org/add-or-remove-object-library-reference-via-vba/