Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    San Francisco, CA, USA
    Thanked 0 Times in 0 Posts

    Access ->Outlook

    I almost have it but have a problem which I beginer stuff but I have not worked with transferring the contents of an OLE object to Outlook using Word as the email editor. I just need to add a paste command to insert the object in the mailitem form but can't seem to figure out the logic.
    I have to code to transfer to an existing work doc or creating a new work doc. But the doc does not exist yet on an Outlook form until the new fax command is invoked.
    Heres the code, Column 3 is a OLE object field, want to copy a doc or excel doc and send to a fax receipent.
    Private Sub cmdCreateInternetMail_Click()

    ' Make sure you have the Outlook library referenced.

    ' Outlook - Create Internet Mail To A Group Of People"

    ' The purpose of this example is to show how to create
    ' an Internet Message to employees in Northwind. Email
    ' can be sent to ALL employees of Northwind or the
    ' employees selected by a query.

    ' There is also a form in the database to add your own
    ' email messages and create your own queries so that
    ' you can use this database to send email to groups of
    ' people.

    Dim objOutlook As Object
    Dim DB As Database
    Dim RS As Recordset
    Dim MailItem As Object
    Dim Attach As String

    'If no item is chosen from the combo box, the user is
    ' given a message.
    If IsNull(Me!cboMailType) Then
    MsgBox "Enter a Mail Type"
    Exit Sub
    End If

    DoCmd.Hourglass True

    ' Use DAO to create a recordset.
    Set DB = DBEngine(0)(0)
    Set RS = DB.OpenRecordset(Me![cboMailType].Column(1), dbOpenSnapshot)

    ' If there is no records returned by the query, give the user
    ' a message box.
    If RS.BOF And RS.EOF Then
    MsgBox "There are no records returned by the query"
    Exit Sub
    End If

    ' Move to the first Employee record

    ' Resume to the next line following the error. This is
    ' necessary because with "GetObject" if Outlook is NOT
    ' open, an error will occur.
    On Error Resume Next

    ' Use GetObject if an application is already running, if not,
    ' use CreateObject. Error 429 occurs with GetObject if Outlook
    ' is not running.
    Set objOutlook = GetObject(, "Outlook.Application")

    ' Error 429 occurs if Outlook is NOT running.
    If Err.Number = 429 Then
    Err.Number = 0
    ' Create a new instance of the Outlook application.
    Set objOutlook = CreateObject("Outlook.Application")
    ' Error 429 will occur if MS Outlook 8.0 is not installed.
    If Err.Number = 429 Then
    MsgBox "MS Outlook 8.0 is not installed on your computer"
    End If
    End If

    ' Loop through all of the records returned by the query.
    Do Until RS.EOF

    ' Create a new Outlook Mail Item.
    Set MailItem = objOutlook.CreateItem(olMailItem)

    ' Insert data into the Outlook mail item.
    With MailItem

    ' In the "To" field, add the email address
    .To = RS!InternetEMail

    ' Add the subject to the Subject field from the
    ' second column of the cboMailType combo box.
    .Subject = Me![cboMailType].Column(2)

    ' Add the body to the body field from the
    ' third column of the combo box. Add a
    ' couple of line feeds so that if a document
    ' is attached, it is after the body of the
    ' message.

    .Body = Me![cboMailType].Column(3) & vbCrLf & vbCrLf

    'NOTE Column 3 is a OLE Object but it does not paste it to the body of the mail item. If I use Ctrl V it pastes.

    ' Add the attachment to the mail item.
    '.Attachments.Add Me![cboMailType].Column(4)

    ' Set the mail items importance to "High"
    ' .Importance = olImportanceHigh

    ' The "Send" method will put the mail items
    ' in the Outbox. To actually send the email
    ' the user needs to choose "Check for New
    ' Mail" from the Tools menu or hit the F5 key.

    End With

    ' Move to the next Employee Record


    ' Inform the user that all email messages have been generated.
    MsgBox RS.RecordCount & " messages have been sent."


    Set RS = Nothing

    ' Release the object variable.
    Set objOutlook = Nothing


    DoCmd.Hourglass False

    End Sub

    Private Sub cmdReadme_Click()

    DoCmd.OpenForm "frmReadMe"

    End Sub

    Sub ExitMicrosoftAccess_Click()

    ' Exit Microsoft Access.

    End Sub
    Sub DisplayDatabaseWindow_Click()

    ' Close Automation Examples form.

    ' Give focus to Database window; select Employees table (first
    ' form in list).
    DoCmd.SelectObject acTable, "Employees", True

    End Sub

    Private Sub Form_Open(Cancel As Integer)

    SendKeys "{F4}"

    End Sub

  2. #2

    Re: Access ->Outlook

    Did you ever figure this out?

    I'm trying to do the same thing, and I'm stuck at the same spot -
    I want to paste part of an excel file into the body of an Outlook mail message.

    I am at the point where I just need Outlook to do the equivalant of pressing Ctrl-V to paste.
    if i manually go to the new message and press Ctrl-V it pastes. Do you know how to automate this?

    Thankyou so much!

Posting Permissions

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