Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2006
    Posts
    126

    Unanswered: eMail from Access

    Since plagiarism is one of the best programming languages, and also one of the best teaching/learning tools, I'm looking for a sample data base that will teach me the fundamentals of sending eMails from an Access application.

    I'm using Access 2003. I will be using Outlook as the mail app to start with, although generalizing to working with other mail apps is the ultimate goal. I'll need to be able to select which of several active eMail accounts in Outlook should be used.

    The app needs to send 3 types of eMail. First is a single eMail to a single client populated with client-specific info stuck into standard boilerplate.

    The second is sending a boilerplate message containing client-specific info to every client, or a selected (query) subset of clients. The third is really a subset of 2, sending this same group a non-client-specific boilerplate message.

    I've looked for such a db on the forum, and doing an inet search. Most of what I found is specific proceedures, not the "get your brain around the concept" start that I would like.

    Any suggestions would be appreciated.
    Pete T.
    PGT

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    Here is an example of e-mailing something from Access to Outlook I have done recently:

    Code:
        Dim stdocname As String
        Dim stEmail As String
        Dim stEmailcc As String
        Dim stEmailbcc As String
        Dim stSubject As String
        Dim stbody As String
        
        stdocname = "frm_ServiceRequestSubmit"
        
        stEmail = Me.Email1 & ";" & Me.Email2
        
        stEmailcc = "russ.myers@bmwna.com; justin.bosman@bmwnaext.com"
        
        stSubject = "Service Request For " & Me!RequestedName & "," & Me!RequestDate
        
        stbody = ("New service request submitted by:  " & vbCrLf & _
        Me!RequestedName & " On " & Me!RequestDate & vbCrLf & _
        vbCrLf & _
        "Service Type:  " & Me!ServiceTypeCombo & vbCrLf & _
        "Problem:  " & Me!Combo61 & vbCrLf & _
        vbCrLf & _
        "Description:  " & Me!Description & vbCrLf & _
        vbCrLf & _
        Me!HotText & vbCrLf & _
        vbCrLf & _
        "Requested completion by:  " & Me!CompletedBy)
        
        DoCmd.SetWarnings False
        DoCmd.SendObject acSendNoObject, , , stEmail, stEmailcc, , stSubject, stbody, , False
        DoCmd.SetWarnings True

  3. #3
    Join Date
    Aug 2006
    Posts
    126
    Thanx chris07tibgs. I've seen several similar procedures. Is it true that the doCmd.SendObject is limited to msgs of <=250 char.? How do you tell Outlook what account to send from? Does Outlook have to be open first before issuing the doCmd.SendObject?

    However, to clarify a little; I currently have a report that has a lot of client-specific data including a sub-report list for the client. In the past, that report has been printed and then snail-mailed. If I could just take the existing report and just send it by eMail that would be great, but it is probably not the "best" way. That is the reason I'm looking for the bigger picture of how Access can work with Outlook. After I get my arms around some competing methods, I can get to the specific code.
    PT
    PGT

  4. #4
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Try using CDO for more flexibility.

    http://www.dbforums.com/microsoft-ac...tus-notes.html

    C

  5. #5
    Join Date
    Aug 2006
    Posts
    126
    Thanks Canupus,
    I'm already starting to read up on CDO, and am starting to get my ancient brain around the whole "eMailing from Access" picture. Thinking way ahead of myself, 2 questions come to mind:
    1. I see, in the CDO examples I've studied, how to set text body to a file or webpage. What sort of referencing would you use for passing a report or report page in Access as the text body?
    2. In Reports you can set the output to PrintPreview, so the output can be looked at for correctness prior to printing. Can you do something similar for CDOeMail?
    Pete
    PGT

  6. #6
    Join Date
    Aug 2006
    Posts
    126
    Canupus,
    btw, thanks for the ref to your previous info. Looks like what I'll try.
    If you have a series of eMails to send to selected clients (select query) do you set up a procedure to call your SendMail from a Do Loop as you cycle thru the query results?
    Pete
    PGT

  7. #7
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Pete--

    To answer your questions:

    1) You can't really set the text body to a report per se. What I usually end up doing is construct HTML code on the fly and insert the data that you need from your query source.

    2) There is no built in "Preview" of the email before you send it. You could always build a form that will display the contents of the email and have a couple of buttons to allow the user to confirm the email send or cancel it.

    If you are sending out a mass email (hopefully not SPAM) then I just create a string of email addresses from the select query. That way you are only sending one email. Otherwise, if you wish to personalize each email I would loop through the select query in code, edit the outgoing email as needed and send the email to each individual email address. Just remember if you have a big recipient list you will be sending an email to each one and the code will take much longer to execute.

    Hope that helps.

    C

  8. #8
    Join Date
    Jul 2006
    Posts
    30
    Pete
    We do something similar but use email merge in Word tied to a query in Access. We have several letters to clients for different purposes. The advantage is that our letterhead and graphics appear in the body of the Outlook email. Looks great. Once the emails have transmitted, we run an update query to change the status for that group. FYI Access 2007/2010 have wizards to send email questionnaires and automatically update responses. We are testing this out and found the outbound works great but have not masterd the auto response return yet.

  9. #9
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85
    Use cdo for the email sending then you have no problem with popups in outlook and print the report to a pdf printer. Save the file and keep the dir and file name in a varstring and use that as an attachment.
    Code I use

    function sendemail(........ optonal the attachment)
    strafz="lodewijk"

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1 ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="namesmptserver
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    .Update
    End With
    strbody = strAfz
    With iMsg
    Set .Configuration = iConf
    .To = RTrim(emailto)
    .cc = RTrim(emailto2)
    .BCC = ""
    .FROM = "...@..."
    .Subject = "test"
    .TextBody = strbody
    .AddAttachment attachbestel
    If RTrim(attachbarcode) <> "" Then
    .AddAttachment attachbarcode
    End If
    If RTrim(attachpakl) <> "" Then
    .AddAttachment attachpakl
    End If
    If RTrim(attachverpak) <> "" Then
    .AddAttachment attachverpak
    End If
    .Send
    End With

    Set iMsg = Nothing
    Set iConf = Nothing

Tags for this Thread

Posting Permissions

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