Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: E-Mailing from Access

    I have a Access 2002 database from which I need to e-mail some data. I have a macro setup which creates the data I want ot e-mail and then attaches it to an e-mail. The bit I am struggling with is predefining the "To:" field with the contents of a table record.....

    This is the process:

    1.. I open a form which displays customers details.
    2.. I then select the customer that I want from a combo box
    3.. Check the details on the selected customer and then click on a cmd button
    4.. A report file is created (via a macro) and attached to an e-mail which has the To: field predefined with the customers e-mail address (which is contained in access).

    Can anyone help ???

  2. #2
    Join Date
    Dec 2003
    Posts
    2
    I basically have a form with a combo box in it, together with other text box's. When I select a record from the combo box all other text box's are updated with the relevant records for that customer. This all works fine !

    I also have a command button on the form which runs the following macro:

    OpenReport
    Close
    OpenQuery
    OpenForm
    SendObject..... This is where it opens an e-mail and attaches the above report.

    What I need to do is at the "SendObject" stage populate the To: field with the contents of a text box from the form (or record from a table).

    I have attached a screen view of the macro to help explain.
    Attached Thumbnails Attached Thumbnails clip.jpg  

  3. #3
    Join Date
    Mar 2003
    Posts
    130
    I would suggest converting the macro to Visual Basic code.

    You can do everthing that's done in a macro using the DoCmd class.

    To send the report:
    Code:
    DoCmd.SendObject acSendReport, "report name", "SnapshotFormat(*.snp)", text1.Text, , , "subject", "message", True
    Where text1 is your text box for the 'To:' field and fill in the report name,
    subject, and message.

  4. #4
    Join Date
    Oct 2003
    Posts
    311
    Sorry dont have much time but i have done something the same, take alook and see if it helps:


    Private Sub SendEmail(File As String)
    Dim O As Outlook.Application
    Dim email As Outlook.MailItem
    Dim myFile As String

    Set O = New Outlook.Application
    Set email = O.CreateItem(olMailItem)
    myFile = direc & File
    With email
    .To = GetAddress(File)
    If .To <> "" Then
    '.BCC=

    If ItsThere(myFile) Then .Attachments.Add myFile, _
    olByValue, 1, File
    .Subject = mo & " 2003 American Express Charges"
    .Body = GetBodyAmex
    .Display 'Can Send if alls well
    Else
    .To = "Kerr@purchase.apollolp.com"
    '.BCC=
    .Subject = mo & "2003 American Express Charges"
    .Body = GetBodyAmex
    If ItsThere(myFile) Then .Attachments.Add myFile, _
    olByValue, 1, File
    .Display
    '.Send

    End If
    End With

    Set email = Nothing
    Set O = Nothing

    End Sub
    Private Function ItsThere(File) As Boolean
    If File <> "" Then
    If Dir(File) <> "" Then
    ItsThere = True
    Else
    ItsThere = False
    End If
    Else
    ItsThere = False
    End If
    End Function
    Private Function GetFiles(direc As String, ii As Integer) As Variant
    Dim f As String
    Dim i As Integer
    Dim msg As String
    Dim Items() As String
    ReDim Items(ii)
    msg = "Files retrieved : " & vbCrLf
    i = 0
    f = Dir(direc & "*.xl?", vbNormal)
    Do While f <> ""
    ' If InStr("MONTH", f) Then
    Items(i) = f
    msg = msg & f & vbCrLf
    i = i + 1
    ' End If
    f = Dir
    Loop
    GetFiles = Items()
    End Function

    Private Function GetBodyAmex() As String
    Dim s As String
    s = vbCrLf & vbCrLf & " Attached are the American Express charges for the month of " & mo & "." & vbCrLf & _
    vbCrLf & "As a procedure when ...."
    vbCrLf & vbCrLf & "Sincerely" & vbCrLf & vbCrLf & "Michael Kerr"

    GetBodyAmex = s

    End Function

    Private Function GetAddress(Person As String) As String

    Select Case Right(Person, 7)
    Case "THY.xls"
    If InStr(Person, "Cathy") Then
    GetAddress = "EmailHere"
    Else
    MsgBox "Error with File :" & Person
    Stop
    End If

    Case "EUR.xls"
    If InStr(Person, "Fleur") Then
    GetAddress = "EmailHere"
    Else
    MsgBox "Error with File :" & Person
    Stop
    End If

    Case "CEY.xls"
    If InStr(Person, "stacey") Then
    GetAddress = "EmailHere"
    Else
    MsgBox "Error with File :" & Person
    Stop

    Debug.Print vbCrLf & "GET EMAIL ADDRESS FOR : " & UCase(Person) & vbCrLf
    End If

    Case "ARA.xls"
    If InStr(Person, "BARBARA") Then
    GetAddress = "EmailHere"
    Else
    MsgBox "Error with File :" & Person
    Stop
    End If
    Case Else
    If InStr(UCase(Person), "MONTH") Or InStr(UCase(Person), "AMEX") Then
    Debug.Print "File : " & Person & " was not sent"
    ElseIf Person = "" Then

    Else
    MsgBox "Error with File :" & Person
    Stop
    End If

    End Select
    End Function

  5. #5
    Join Date
    Mar 2003
    Posts
    130
    Your code won't create a .snp Snapshot File from a report which is
    what sv1 needs.

    It's a whole lot easier just to use DoCmd.SendObject in a lot fewer lines
    of code.

Posting Permissions

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