Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007

    Unanswered: Generating/Sending Emails with MSAccess

    Hi, i have searched the forums before i posted this, and the following thread is almost exactly what i wanted to do.

    My problem:

    I have a (Hypothetical-example) database of records of Patients and Doctors.

    Each day, the doctor would want a Report on the patients he/she has seen today.

    So far, that has been easily done with a Query and a Report generated through MS Access.

    My problem is to Generate a email and send it to the Recipients (Doctors), note. i want to send it to more than one doctor.

    Ive searched around an there are links like this:
    where they use a script:
    Sub sendEmail()
    Dim olk As Outlook.Application
    Dim pst As Outlook.MailItem
    Dim rst As Recordset
    Dim db As Database
    Dim usr As Variant
    Set db = CurrentDb
    Set rst = db.OpenRecordset("ASPComponentShipped")
    Set olk = CreateObject("outlook.application")
    With rst
    Do Until .EOF
    usr = ""
    Set pst = olk.CreateItem(olMailItem)
    With pst
    .ReadReceiptRequested = False
    .To = usr
    .Subject = "ASP Component Shipped"
    .Body = "EAI Gemini Support," & vbCrLf & vbCrLf &
    "The following ASP component requests were fullfilled today:" _
    & vbCrLf & vbCrLf & Rack & " " & Companyname & " "
    & ComponentName _
    & vbCrLf & vbCrLf & "Sincerely," & vbCrLf & "EPI
    Gemini Support"
    End With
    End With
    End Sub
    I'm assuming that is vB script, and here is where i stumble.
    I have never used vB, i've used other languages, and coldfusion to muck around with Databases and such.

    So my question is, if i want set up such a thing in MS Access without using a dynamic page like PHP or ColdFusion, that is the correct way right?

    Can anyone provide a simple code for me.

    Assuming that the following are available and in a relational database:
    "Doctor ID, FirstName, LastName, Email" and "PatientID, FirstName, LastName"

    Where a History Table joins the Doctor to their Patients.

    Thanks heaps!!! =)

    I was reading this article:

    Do i need outlook express?? (well i use it)
    I guess you'll have to explain to me how that works with Access aswell then.
    Sorry ><
    Last edited by NeoDeGenero; 12-10-07 at 19:01.

  2. #2
    Join Date
    Dec 2007
    After Browsing around, i found a nice piece of code that MS provided,
    it sends emails fine, now i guess i'll just have to figure out all of its functionalities and send that report out.


    Do this in modules:
     Option Explicit
    Sub SendMessage(Optional AttachmentPath)
       Dim objOutlook As Outlook.Application
       Dim objOutlookMsg As Outlook.MailItem
       Dim objOutlookRecip As Outlook.Recipient
       Dim objOutlookAttach As Outlook.Attachment
       ' Create the Outlook session.
       Set objOutlook = CreateObject("Outlook.Application")
       ' Create the message.
       Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
       With objOutlookMsg
          ' Add the To recipient(s) to the message.
         Set objOutlookRecip = .Recipients.Add("Liu, George")
          objOutlookRecip.Type = olTo
          ' Add the CC recipient(s) to the message.
          Set objOutlookRecip = .Recipients.Add("Liu, George")
          objOutlookRecip.Type = olCC
          ' Set the Subject, Body, and Importance of the message.
          .Subject = "This is an Automation test with Microsoft Outlook"
          .Body = "Last test - I promise." & vbCrLf & vbCrLf
          .Importance = olImportanceHigh  'High importance
          ' Add attachments to the message.
          If Not IsMissing(AttachmentPath) Then
             Set objOutlookAttach = .Attachments.Add(AttachmentPath)
          End If
          ' Resolve each Recipient's name.
          For Each objOutlookRecip In .Recipients
             If Not objOutlookRecip.Resolve Then
          End If
       End With
       Set objOutlookMsg = Nothing
       Set objOutlook = Nothing
    End Sub
    Though, i dont know why i'm not getting two copies of the email. Since there is an CC there. =/

  3. #3
    Join Date
    Dec 2007
    I went around to more pages on the net, and this is sort of what i wanted to do, just to clarify things.

    I am trying to send a Report generated by MS Access via email through the command SendObject.

    DoCmd.SendObject acSendReport, "Practitioners2", acFormatXLS, _
    "George Liu;", "George Liu", , _
    "SubjectLine", , False

    I’m not sure exactly how to structure the whole VBA code to access the database, and searching around, there aren’t a complete code.

    I’m not even sure what the _ does in the code above.

    Anyway, you have seen my example database with Doctors and Patients.
    The report generates a list of the patients the doctor has seen and I want to send it off to their emails in Excel format

    I’m currently stuck on how to send it to one Doctor, and I will then need to send it to multiple doctors with multiple (different) Reports.

  4. #4
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    This page and the pages it links to should help:

    The underscore is the line continuation character.

Posting Permissions

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