Basic how to use Outlook from Excel question - VBA vs VBScript

[bnj1776]

I've been slowly building my understanding of using Excel to access Outlook by searching the internet and trying tons of examples to put together what I'm after.

HOWEVER...

I ran across the following at CreateItem Method

The following Microsoft Visual Basic/Visual Basic for Applications (VBA) example creates a new MailItem object and sets the BodyFormat property to olFormatHTML. The Body text of the e-mail item will now appear in HTML format."

and

If you use Microsoft Visual Basic Scripting Edition (VBScript) in a Microsoft Outlook form, you do not create the Application object, and you cannot use named constants. This example shows how to create a contact item in the default Contacts folder using VBScript code.

I'm using Excel and Outlook 2013 via default install.

The only Outlook code I can get to work from Excel though is the VBScript type.
For example, this works:

Dim myOutlook As Object    
Set myOutlook = CreateObject("Outlook.Application")

But this does not:

Dim olApp As Outlook.Application

I've not been able to find a clear, "flip this switch on to use Outlook" as in the VBA Outlook.
What in the world am I missing here???

Also, I need to distribute this workbook to other users who are not technical at all. Does this mean I should stick only to using Outlook via VBScript?

I am specifically after the following:
1. Open a filtered (via the "User1" field equal "Customer") contacts window without the To, CC, BCC fields (got that working)
2. Using that selected contact above (via email address?) to pull in the full contact information to use in Excel (not even close yet)

It is clear that the VBScript version is not a functional as the VBA version of using Outlook in Excel.
And it appears to me that I'll not be able to do what I need via the VBScript.

Would someone please point me to a clear explanation of the differences between the two methods of accessing Outlook from Excel.
Please tell me how to switch on the VBA Outlook or point me to something that will, because I'm simply not finding it.

Lastly experts, which method is better for what I need to do? VBA Outlook or am I really missing something?

Thank you!
Brian

 

[bnj1776]

Here is my test for pulling in the correct contact (fills a worksheet via " .Restrict ("[User1] = 'Customer' "):

Option Explicit

Sub Import_Contacts()


'   Outlook objects:
'       Dim olApp As Outlook.Application
        Dim olApp                           As Object
    
'       Dim olNamespace As Outlook.Namespace
        Dim olNamespace                     As Object
    
'       Dim olFolder As Outlook.MAPIFolder
        Dim olFolder                        As Object
    
'       Dim olConItems As Outlook.Items
        Dim olConItems                      As Object
        Const olFolderContacts              As Integer = 10 'Outlook's Enumeration for Contacts
   
'       Dim NewTask As Outlook.TaskItem
        Dim olTaskItem                      As Object
        
        Dim olItem                          As Object
    
'   Excel objects:
        Dim wbBook                          As Workbook
        Dim wsSheet                         As Worksheet
    
'       Location in the imported contact list
        Dim lnContactCount                  As Long
    
        Dim strDummy                        As String
    
'   Initalize the Outlook variables with the MAPI namespace and the default Outlook folder of the current user.
    Set olApp = CreateObject("Outlook.Application")
    Set olNamespace = olApp.GetNamespace("MAPI")
    Set olFolder = olNamespace.GetDefaultFolder(olFolderContacts)
    Set olConItems = olFolder.Items
    
'   Turn off screen updating
    Application.ScreenUpdating = False
    
'   Initialize the Excel objects
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets(1)
    
'   Format the target worksheet
    With wsSheet
        .Range("A1").CurrentRegion.Clear
        .Cells(1, 1).Value = "Company / Private Person"
        .Cells(1, 2).Value = "Street Address"
        .Cells(1, 3).Value = "Postal Code"
        .Cells(1, 4).Value = "City"
        .Cells(1, 5).Value = "Contact Person"
        .Cells(1, 6).Value = "E-mail"
        With .Range("A1:F1")
            .Font.Bold = True
            .Font.ColorIndex = 10
            .Font.Size = 11
        End With
    End With
    
    wsSheet.Activate
    
'    Set olApp = CreateObject("Outlook.Application")
'    Set olNamespace = olApp.GetNameSpace("MAPI")
'    Set olFolder = olNamespace.GetDefaultFolder(10)
'    Set olConItems = olFolder.items
            
'   Row number to place the new information on; starts at 2 to avoid overwriting the header
    lnContactCount = 2
    
'   For each contact: if it is a business contact, write out the business info in the Excel worksheet;
'   otherwise, write out the personal info.
    For Each olItem In olConItems
        If TypeName(olItem) = "ContactItem" Then
            With olItem
                .Restrict ("[User1] = 'Customer'")
'                If InStr(olItem.CompanyName, strDummy) > 0 Then
                    Cells(lnContactCount, 1).Value = .CompanyName
                    Cells(lnContactCount, 2).Value = .BusinessAddressStreet
                    Cells(lnContactCount, 3).Value = .BusinessAddressPostalCode
                    Cells(lnContactCount, 4).Value = .BusinessAddressCity
                    Cells(lnContactCount, 5).Value = .FullName
                    Cells(lnContactCount, 6).Value = .Email1Address
'                Else
                    Cells(lnContactCount, 8).Value = .FullName
                    Cells(lnContactCount, 9).Value = .HomeAddressStreet
                    Cells(lnContactCount, 10).Value = .HomeAddressPostalCode
                    Cells(lnContactCount, 11).Value = .HomeAddressCity
                    Cells(lnContactCount, 12).Value = .FullName
                    Cells(lnContactCount, 13).Value = .Email1Address
                    Cells(lnContactCount, 14).Value = .User1
'                End If
'                wsSheet.Hyperlinks.Add Anchor:=Cells(lnContactCount, 6), _
'                                       Address:="mailto:" & Cells(lnContactCount, 6).Value, _
'                                       TextToDisplay:=Cells(lnContactCount, 6).Value
                
                Cells(lnContactCount, 7).Value = .LastModificationTime
            
                If .CustomerID = vbNullString Then
                    .CustomerID = lnContactCount
                End If
                
                On Error Resume Next
                .Save
            
            End With
            lnContactCount = lnContactCount + 1
        End If
    Next olItem
    
    
    
'   Null out the variables.
    Set olItem = Nothing
    Set olConItems = Nothing
    Set olFolder = Nothing
    Set olNamespace = Nothing
    Set olApp = Nothing
    
'   Sort the rows alphabetically using the CompanyName or FullName as appropriate, and then autofit.
    With wsSheet
        .Range("A2", Cells(2, 6).End(xlDown)).Sort key1:=Range("A2"), order1:=xlAscending
        .Range("A:F").EntireColumn.AutoFit
    End With
            
'   Turn screen updating back on.
    Application.ScreenUpdating = True
    
    MsgBox "The list has successfully been created!", vbInformation
    
End Sub

Here is my partially working function:

Function GetFilteredContacts(myContactItem As String, myContactValue As String) As Variant'   Pass in the contact field to restrict the search to and the value to find and get back full list of matching contacts


    Dim myOutlook As Object
    Set myOutlook = CreateObject("Outlook.Application")
    
    Dim myNamespace As Object
    Set myNamespace = myOutlook.GetNamespace("MAPI")


    Dim myContacts As Object
    Const olFolderContacts As Integer = 10 'Outlook Enumeration for Contacts Folder
    Set myContacts = myNamespace.GetDefaultFolder(olFolderContacts).Items
    
    Dim myItems As Object
    Set myItems = myContacts.Restrict("[" & myContactItem & "] = '" & myContactValue & "'")
'   Debug.Print "[" & myContactItem & "] = '" & myContactValue & "'"
    
    Dim myItem As Object
    Const olContact As Integer = 40 'Outlook Enumeration for olObjectClass.ContactItem


'   For Each myItem In myItems
'       Debug.Print myItem.class
'       If (myItem.class = olContact) Then
'           Debug.Print myItem.FullName & ": " & myItem.LastModificationTime
'       End If
'   Next


    Set GetFilteredContacts = myItems
End Function

 

[John_w]

I ran across the following at CreateItem Method

That is specifically for an Outlook form, using VBScript to respond to click events that are fired by controls on the form page. Nothing to do with Excel, or other uses of VBA in Outlook. See also About Using VBScript in Outlook for another explanation. The restriction "you cannot use named constants defined in the Outlook object type library" simply means that Outlook constants such as olFolderContacts are not automatically defined and therefore you must define them in the VBScript code: Const olFolderContacts = 10.

I've not been able to find a clear, "flip this switch on to use Outlook" as in the VBA Outlook.
What in the world am I missing here???

In the Excel VBA editor, click Tools - References and tick Microsoft Outlook nn.0 Object Library. You'll then be able to declare variables for Outlook classes such as Outlook.Application and Outlook.MailItem in Excel VBA code and get the intellisense help showing the related properties and methods as you type the variable name in the VBA editor.

Also, I need to distribute this workbook to other users who are not technical at all. Does this mean I should stick only to using Outlook via VBScript?

If you are using Excel as an interface or database to retrieve/store/update Outlook data then it makes sense to use Excel VBA with Outlook classes and object variables as explained in my previous paragraph. Using VBScript with Outlook (I'm not talking about the special case of an Outlook form here) is typically done when you don't need a visible interface, e.g. a scheduled task running in the background.

Hopefully that answers your questions.

 

[bnj1776]

thanks, knew i had to be missing something

 

[bnj1776]

follow up question here: http://www.mrexcel.com/forum/excel-...tselectnamesdialog-questions.html#post3970201