Results 1 to 5 of 5
  1. #1
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 229 Times in 210 Posts

    Re: Export Access to Outlook

    The property you need is the Body property. You haven't said what you want to write to the notes field so I can't be too specific but in your code, somwhere between the
    With itm and End With lines you need to put something like
    .Body = "Whatever text you want here"
    If you need any more explanation please post again.

    Microsoft MVP - Excel

  2. #2

    Export Access to Outlook

    Hi to everybody!

    I'm from Austria (Europa) and therefore please excuse my English. Hopefully you will understand my problem.

    I have downloaded a code sample from home page of Helen Feddema. It is exporting data from an access data base to an Outlook contact file and it works prety good.

    This is the VBA Code

    VBA Code
    Option Compare Database
    Option Explicit

    Declare the pfld variable as Public so it can be set in one procedure and used in another
    Public pfld As Outlook.MAPIFolder
    Dim appOutlook As Outlook.Application
    Dim nms As Outlook.NameSpace

    Private Sub cmdSelectFolder_Click()

    Call SelectFolder

    End Sub

    Function SelectFolder()

    On Error GoTo ErrorHandler

    Set appOutlook = CreateObject("Outlook.Application")
    Set nms = appOutlook.GetNamespace("MAPI")

    Set pfld = nms.PickFolder
    Debug.Print "Default item type: " & pfld.DefaultItemType
    If pfld.DefaultItemType <> olContactItem Then
    MsgBox "Please select a Contacts folder"
    GoTo SelectContactFolder
    End If

    Me![txtFolderName].Value = pfld.Name
    Me![LastContact].Value = ""

    Exit Function

    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit

    End Function

    Private Sub cmdExport_Click()

    On Error GoTo ErrorHandler

    Dim dbs As Database
    Dim rst As Recordset
    Dim itms As Outlook.Items
    Dim itm As Outlook.ContactItem
    Dim strTitle As String
    Dim strFirstName As String
    Dim strMiddleName As String
    Dim strLastName As String
    Dim strSuffix As String
    Dim strJobTitle As String
    Dim strCompany As String
    Dim strLastNameFirst As String
    Dim strBusinessStreet As String
    Dim strBusinessStreet2 As String
    Dim strBusinessCity As String
    Dim strBusinessState As String
    Dim strBusinessPostalCode As String
    Dim strBusinessCountry As String
    Dim strBusinessPhone As String
    Dim strBusinessFax As String
    Dim strHomeStreet As String
    Dim strHomeStreet2 As String
    Dim strHomeCity As String
    Dim strHomeState As String
    Dim strHomePostalCode As String
    Dim strHomeCountry As String
    Dim strHomePhone As String
    Dim strHomeFax As String
    Dim strOtherStreet As String
    Dim strOtherStreet2 As String
    Dim strOtherCity As String
    Dim strOtherState As String
    Dim strOtherPostalCode As String
    Dim strOtherCountry As String
    Dim strOtherPhone As String
    Dim strOtherFax As String
    Dim strEMailAddress As String
    Dim strEMailAddress2 As String
    Dim strContactID As String
    Dim lngCount As Long
    Dim strMessage As String
    Dim lngResult As Long
    Dim strContactForm As String
    Dim dteLastVisit As Date
    Dim intNoChildren As Integer
    Dim strCustomerType As String
    Dim strCategory As String
    Dim varReturn As Variant
    Dim lngPosition As Long

    If Folder Name textbox is blank, call function to select folder
    If Me![txtFolderName].Value = "" Then Call SelectFolder
    Set itms = pfld.Items

    Set reference to Access table containing contact data
    Set dbs = CurrentDb
    Set rst = dbs![tblContacts].OpenRecordset(dbOpenTable, dbDenyRead)
    lngCount = rst.RecordCount
    strMessage = lngCount & " contact records to transfer to Outlook -- proceed?"

    Ask if user wants to proceed with the export
    lngResult = MsgBox(strMessage, vbYesNo, "Proceed?")

    Exit if user says No
    If lngResult = vbNo Then Exit Sub

    Pick up name of contact form from text box, with IPM.Contact.Test Contact as a default in case the text box is blank. If this form (or any specified form) is not available, the standard Contact form will be used instead
    strContactForm = Nz(Me![txtContactForm], "IPM.Contact")

    Pick up category from text box, allowing a blank category
    strCategory = Nz(Me![txtCategory])

    Turn on hourglass and initialize status bar to show progress of the export
    DoCmd.Hourglass True
    strMessage = "Exporting " & lngCount & " records to Outlook"
    varReturn = Application.SysCmd(acSysCmdInitMeter, strMessage, lngCount)
    Me![LastContact].Value = ""

    Loop through the Access table, exporting each record to Outlook
    The Nz function is used to convert blanks into zeros or zero-length strings, in some cases supplying default values
    The vbCrLf constant represents a carriage return + linefeed
    For lngPosition = 1 To lngCount
    With rst
    'Set variables to data from a record
    strContactID = Nz(![CustomerID])
    strTitle = Nz(![Title])
    strFirstName = Nz(![FirstName])
    strMiddleName = Nz(![MiddleName])
    strLastName = Nz(![LastName])
    strSuffix = Nz(![Suffix])
    strJobTitle = Nz(![JobTitle])
    strCompany = Nz(![Company])
    strLastNameFirst = Nz(![LastName]) & ", " & Nz(![FirstName])
    strBusinessStreet = Nz(![BusinessStreet1]) & IIf(Nz(![BusinessStreet2]) <> "", vbCrLf & Nz(![BusinessStreet2]), "")
    strBusinessCity = Nz(![BusinessCity])
    strBusinessState = Nz(![BusinessState])
    strBusinessPostalCode = Nz(![BusinessPostalCode])
    strBusinessCountry = Nz(![BusinessCountry])
    strBusinessPhone = Nz(![BusinessPhone])
    strBusinessFax = Nz(![BusinessFax])
    strHomeStreet = Nz(![HomeStreet1]) & IIf(Nz(![HomeStreet2]) <> "", vbCrLf & Nz(![HomeStreet2]), "")
    strHomeCity = Nz(![HomeCity])
    strHomeState = Nz(![HomeState])
    strHomePostalCode = Nz(![HomePostalCode])
    strHomeCountry = Nz(![HomeCountry])
    strHomePhone = Nz(![HomePhone])
    strHomeFax = Nz(![HomeFax])
    strOtherStreet = Nz(![OtherStreet1]) & IIf(Nz(![OtherStreet2]) <> "", vbCrLf & Nz(![OtherStreet2]), "")
    strOtherCity = Nz(![OtherCity])
    strOtherState = Nz(![OtherState])
    strOtherPostalCode = Nz(![OtherPostalCode])
    strOtherCountry = Nz(![OtherCountry])
    strOtherPhone = Nz(![OtherPhone])
    strOtherFax = Nz(![OtherFax])
    strEMailAddress = Nz(![E-mailAddress])
    strEMailAddress2 = Nz(![E-mail2Address])

    1/1/4501 is the way Outlook stores a blank date
    dteLastVisit = Nz(![LastVisit], #1/1/4501#)
    intNoChildren = Nz(![NumberChildren])
    strCustomerType = Nz(![CustomerType], "Standard")

    End With

    Create a contact item
    Set itm = itms.Add(strContactForm)

    Write values from variables to fields in the new Contact item
    With itm
    Standard Contact fields
    .CustomerID = strContactID
    .Title = strTitle
    .FirstName = strFirstName
    .MiddleName = strMiddleName
    .LastName = strLastName
    .Suffix = strSuffix
    .JobTitle = strJobTitle
    .CompanyName = strCompany
    .BusinessAddressStreet = strBusinessStreet
    .BusinessAddressCity = strBusinessCity
    .BusinessAddressState = strBusinessState
    .BusinessAddressPostalCode = strBusinessPostalCode
    .BusinessAddressCountry = strBusinessCountry
    .BusinessTelephoneNumber = strBusinessPhone
    .BusinessFaxNumber = strBusinessFax
    .HomeAddressStreet = strHomeStreet
    .HomeAddressCity = strHomeCity
    .HomeAddressState = strHomeState
    .HomeAddressPostalCode = strHomePostalCode
    .HomeAddressCountry = strHomeCountry
    .HomeTelephoneNumber = strHomePhone
    .HomeFaxNumber = strHomeFax
    .OtherAddressStreet = strOtherStreet
    .OtherAddressCity = strOtherCity
    .OtherAddressState = strOtherState
    .OtherAddressPostalCode = strOtherPostalCode
    .OtherAddressCountry = strOtherCountry
    .OtherTelephoneNumber = strOtherPhone
    .OtherFaxNumber = strOtherFax
    .Email1Address = strEMailAddress
    .Email2Address = strEMailAddress2
    .Categories = strCategory

    Custom fields -- modify as needed for your custom form
    The Debug.Print statements are for debugging possible problems
    Debug.Print "Writing " & dteLastVisit & " to Last Visit field"
    .UserProperties("LastVisit") = dteLastVisit
    Debug.Print "Writing " & intNoChildren & " to Number Children field"
    .UserProperties("NumberChildren") = intNoChildren
    Debug.Print "Writing " & strCustomerType & " to Customer Type field"
    .UserProperties("CustomerType") = strCustomerType

    Close and save new contact item
    .Close (olSave)
    DoCmd.RunCommand acCmdSaveRecord

    Update status bar with progress
    varReturn = Application.SysCmd(acSysCmdUpdateMeter, lngPosition)

    Write information about contact just processed to a textbox on the form
    Me![LastContact] = strContactID & " -- " & strLastNameFirst
    End With
    Next lngPosition

    Clear status bar and turn off hourglass
    varReturn = Application.SysCmd(acSysCmdClearStatus)
    DoCmd.Hourglass False

    MsgBox "All Contacts exported!"

    Exit Sub

    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit

    End Sub

    Private Sub cmdQuit_Click()

    On Error GoTo ErrorHandler


    Exit Sub

    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit

    End Sub

    Private Sub Form_Load()

    On Error GoTo ErrorHandler

    DoCmd.RunCommand acCmdSizeToFitForm
    Me![LastContact] = ""
    Me![FolderName] = ""
    Me![Category] = ""

    Exit Sub

    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit

    End Sub

    The only problem is, that I am not able to extend the code for the "notes" field in Outlook. That means, I am not able to write at this field.

    Is there anybody who can extend the code sample, including the necessary codes for writing to the "notes" field in Outlook?

    Many thanks for your help. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  3. #3

    Re: Export Access to Outlook

    Thanks for your help, Rory!

    I have added the complete example access data base. There you will find the table "tblContacts" and there the field "notes". The contents of this field should be exported to the "notes" field of a contact form at outlook.

    I am able to extend the VBA code, to write at any other contact form field, whatever I want, except writing in notes field.

    It seems, that the contact field is not part of the contact form. I am right? But if it is so, I don't know how to extend the VBA code.

    If you could have a look at my problem, it would be very kind.


  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 229 Times in 210 Posts

    Re: Export Access to Outlook

    I've attached the extended code as a text file - I haven't had a chance to test it but it should be OK. Let me know if you have any problems with it.
    Attached Files Attached Files

    Microsoft MVP - Excel

  5. #5

    Re: Export Access to Outlook

    Hi rory!

    Thank very much you for thinking over my problem and solving it!!!!!

    Your code extension is working very well.

    Best regards and have a nice day.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts