Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2006
    Posts
    35

    Unanswered: Emailing functions

    Is there an elegant way to create a macro or script that would capture record information and send an email to a defined recipient? For example,
    if information is recorded into the database, the user would click on a button that would send an automatic notification to the source that the information is in the system and a tracking number is given.

    For example:
    Data is entered, a notify button is clicked.
    An email is sent to the originator: Subject=[data]![requestsummary]
    "We have received your request regarding
    [information on field1]
    [information on field2]

    Your tracking number is [trackingnumber]. Please refer to this number if you have any questions about the status of your request."

    I have all of the info and the concept but the details remain ellusive. Any ideas of syntax assistance appreciated.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Define your subject and body as string variables, build them dynamically and pass them to the sendobject function. A lot of people miss that sendobject doen't REQUIRE an object, you can send a normal email too...

    If you want to automatically SEND the email without user interaction, that becomes infinantly more complex, as you now have to incorporate an entire mail agent into your project instead of using the default mail client on the user's machine.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    emailing

    We used to use the sendobject command but our anti-virus software kept coming up and asking if the user still wanted to send the attachment and the user had to select yes or no.

    We now use blat and send the report as a snapshot attachment which seems to work very well. blat I believe is free and fairly easy to install. Here's what our code looks like to send the email (getuser gets the user's login - see my post on getting the user's login):

    Public Sub SendEmail(ToVar As String, EmailAttch As String, PONumber As Variant)
    Dim FromVar, Server, FileToSend, OverrideBody, Subject As String
    FromVar = GetUser() & "@weccusa.org"
    Subject = "POReportEmail - " & PONumber
    OverrideBody = "Purchase Order Billed To Muni/Co-op"
    Server = "linux2.weccusa.org"
    FileToSend = "\\SQLServer\Databases\Databases\blatEmail\message .txt"

    Dim x As String

    'send email

    p_blat_location = "\\SQLServer\Databases\Databases\blatEmail\blat.ex e"

    'MsgBox (Attachment)
    'MsgBox EmailAttch

    x = p_blat_location & " " & FileToSend & " -s " & Chr(34) & Subject & Chr(34) & _
    " -t " & ToVar & " -f " & FromVar & " -server " & Server & " -attach " & EmailAttch

    'x = p_blat_location & " " & FileToSend & " -s " & Chr(34) & Subject & Chr(34) & _
    ' " -t " & ToVar & " -f " & FromVar & " -server " & Server & " -attach \\SQLServer\Databases\Databases\PurchaseOrders\POE mails\17725.snp"

    If Len(OverrideBody) > 0 Then
    x = x & " -body " & Chr(34) & OverrideBody & Chr(34)
    End If

    'debug
    x = x & " -debug -log c:\blat.log -timestamp"

    'MsgBox x
    'Debug.Print x

    'Shell x, vbHide

    Set WshShell = CreateObject("WScript.Shell")
    WshShell.Run "cmd.exe /c " & x, 0, True
    Set WshShell = Nothing

    End Sub
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    emailing

    We used to use the sendobject command but our anti-virus software kept coming up and asking if the user still wanted to send the attachment and the user had to select yes or no.

    We now use blat and send the report as a snapshot attachment which seems to work very well. blat I believe is free and fairly easy to install. Here's what our code looks like to send the email (getuser gets the user's login - see my post on getting the user's login):

    Public Sub SendEmail(ToVar As String, EmailAttch As String, PONumber As Variant)
    Dim FromVar, Server, FileToSend, OverrideBody, Subject As String
    FromVar = GetUser() & "@weccusa.org"
    Subject = "POReportEmail - " & PONumber
    OverrideBody = "Purchase Order Billed To Muni/Co-op"
    Server = "linux2.weccusa.org"
    FileToSend = "\\SQLServer\Databases\Databases\blatEmail\message .txt"

    Dim x As String

    'send email

    p_blat_location = "\\SQLServer\Databases\Databases\blatEmail\blat.ex e"

    'MsgBox (Attachment)
    'MsgBox EmailAttch

    x = p_blat_location & " " & FileToSend & " -s " & Chr(34) & Subject & Chr(34) & _
    " -t " & ToVar & " -f " & FromVar & " -server " & Server & " -attach " & EmailAttch

    'x = p_blat_location & " " & FileToSend & " -s " & Chr(34) & Subject & Chr(34) & _
    ' " -t " & ToVar & " -f " & FromVar & " -server " & Server & " -attach \\SQLServer\Databases\Databases\PurchaseOrders\POE mails\17725.snp"

    If Len(OverrideBody) > 0 Then
    x = x & " -body " & Chr(34) & OverrideBody & Chr(34)
    End If

    'debug
    x = x & " -debug -log c:\blat.log -timestamp"

    'MsgBox x
    'Debug.Print x

    'Shell x, vbHide

    Set WshShell = CreateObject("WScript.Shell")
    WshShell.Run "cmd.exe /c " & x, 0, True
    Set WshShell = Nothing

    End Sub
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jan 2006
    Posts
    35
    Thanks!
    This is a good start. We shall see how it goes.

  6. #6
    Join Date
    Jan 2006
    Posts
    35
    Hi all,
    I have tinkered a bit with the code, and here is what I have come up with that seems to work. I used the NZ() function instead of IF statements to handle the records containing no data which would result in a macro error. The string strText is a bit unweildy visually but in this case it works

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strTO As String
    Dim strSubject As String
    Dim strTitle As String
    Dim strText As String
    Dim docnum1 As String
    Dim docnum2 As String
    Dim docnum3 As String
    Dim docnum4 As String

    strTO = Nz(Me.ReqEmail, "No Email")
    docnum1 = Nz(Me.TrackingNum, "0")
    docnum2 = Nz(Me.Num2, "0")
    docnum3 = Nz(Me.OtherNum, "0")
    docnum4 = Me.ReqNumber
    strTitle = Nz(Me.Title, "Sent Request")
    strSubject = "Notification: Your Purchase Request with this office has been Processed."

    strText = "The request regarding: " & strTitle & " has been Processed." & _
    Chr(13) & Chr(13) & "Please use the below numbers when contacting this office:" & _
    Chr(13) & Chr(13) & "Tracking Number: " & docnum1 & Chr(13) & "Document Number: " & _
    docnum4 & Chr(13) & "MARS Number: " & docnum2 & Chr(13) & "Other Number: " & docnum3 & _
    Chr(13) & "Please contact the Office of Contract Oversight at xxx-xxxx"

    DoCmd.SendObject acSendNoObject, " ", "HTML", strTO, , , strSubject, strText, True

  7. #7
    Join Date
    Jul 2005
    Location
    Northern California
    Posts
    21

    Sending Email from database

    This is very similar (or the same) to what we want to do at my project. We have an Access 97 database to track "Issues." Issues are incidents that have come up in the program we support, that are no longer actual trouble tickets as they have been identified as Functions As Designed. If this is not acceptable, they are converted to "Issues" and forwarded to one of three workgroups depending on the category of the Issue.

    Currently, we have an Outlook 2000 form that we use to forward the Issue to the appropriate workgroup. The information in the email is then copied into an Access 97 form to update the database so that we can track it. This requires the staff to duplicate efforts.

    The idea has come about that perhaps staff could just enter the information into the database form, and have the email generated from there. This seems quite reasonable. One question though, will Access 97 and Outlook 2000 play together nicely?

    Anyhow, the task of investigating a solution has fallen to me. I wouldn't call myself a Novice with Access, but I wouldn't say I'm quite up to Intermediate level of experience, either.

    Anyhow, one of the first places I thought of going to is the forum here.

    So, after looking a little bit under what has been posted, I found your question on only like the 3rd page, very similar to my needs.

    My question is - what are you using for email, and what is the output? Looking at a couple of the suggestions that the other respondents gave you, it looks like they have the output go as an attachment. That would not work for our needs, as we need plain text to go into the body of the email. Additionally, we cannot have the email be automatically sent, because the staff will also need to add an attachment (screenshots) to the email before sending.

    Are you using Outlook. If not, any idea if the code you have developed might work regardless of the Mail system used? And, is your output plain text into the message body? (as I glanced over it, it looks like it might be.)

    Thanks.
    Cor
    {Currently working with Access 97, so most questions will be from that point-of-view}

  8. #8
    Join Date
    Jan 2006
    Posts
    35
    We are using XP Office 2002, workstations running XP Pro, and McAfee Virusscan Enterprise. Outlook is our email client. There hasnt been a problem as noted in other posts with email and viruscan conflicts/messages. I will have to update the references in all of the workstations so that Access will recognize the some of the source code commands.

    The code is a string that is input into the fields as plain text. I wish it were formattable but am satisified without. There are no attachments in the message that is created.
    Last edited by acrumbacker; 02-10-06 at 14:51.

  9. #9
    Join Date
    Feb 2006
    Posts
    56
    I wrote several applications for my old company that accomplished exactly what you want to do as described above.

    did you try using automation with outlook to send the email? Its fairly simple. I haven't had any issues automating access with outlook, except for the fact that if you try to access an email address (even from your PC) you will get notified that someone or something is trying to access your email application.

    It brings up a notification box and gives you the ability to grant access. Thanks to our deviant friends who like to hack, you cannot set the system warnings off to disable this feature.

  10. #10
    Join Date
    Feb 2006
    Posts
    56
    also with automation, you can format your text as well.

  11. #11
    Join Date
    Jan 2006
    Posts
    35
    JF:
    I think when you set the sendobject function to true, the message is not automatically sent since you can edit the message. If this parameter is not set, then the message will go as you describe (at least with our systems).

    Formatting the text is something that I would be interested in. How is this accomplished?

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Emailing Via Outlook

    If you try sending an email with an attachment via Outlook and the sendobject command, depending on your virus software is setup, you may get a popup asking the user to confirm sending the email each time. This may not be ideal as the user can always select No.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by John_Farren
    I haven't had any issues automating access with outlook, except for the fact that if you try to access an email address (even from your PC) you will get notified that someone or something is trying to access your email application.

    It brings up a notification box and gives you the ability to grant access. Thanks to our deviant friends who like to hack, you cannot set the system warnings off to disable this feature.
    I believe Wayne posted a solution to this.... it was some time ago and I can't find it in the archives (there's a surprise). I can't actually remember the solution - only that there was one. Knowing Wayne, it was probably some obscure and very technical fix.

    Terribly useful to you eh?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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