Set up alert mail

[FMa12]

Hi everybody,

I would like the following.

I have a table with the following fields:
"ID", "Date", "Client", "AssignedTo", "ClientActivityDescription" und "NextActionDueUntil"; "ActionRequired".

I would like to set up an alert mail system which sends an E-Mail to the "AssignedTo" Person 1 day before the "NextActionDueUntil" date. The body of the mail should include the entries in all the fields.

Can someone help?

Thanks

 

[NigelShaw]

Hi everybody,

I would like the following.

I have a table with the following fields:
"ID", "Date", "Client", "AssignedTo", "ClientActivityDescription" und "NextActionDueUntil"; "ActionRequired".

I would like to set up an alert mail system which sends an E-Mail to the "AssignedTo" Person 1 day before the "NextActionDueUntil" date. The body of the mail should include the entries in all the fields.

Can someone help?

Thanks

Hi,

what have you attempted so far?

N

 

[CBrighton]

:Edit: The following post assumes Outlook is the email client in use

To have it fully automated you will need to add a module to both Access & Outlook, sign the code in Outlook, and use the Access function to call the Outlook function to send an email if a query returns 1 or more records.

You will also need a way to ensure it's run just once a day (a 1 field table should be able to do that, tell it to add a record with today's date just before it sends the email and have the begining of the process check to make sure there isn't already a record with today's date).

If you want to do this the Access code to go into a new module is:

'This is the procedure that calls the Outlook VBA function...
Public Function SendEmail(strTo As String, _
                    strSubject As String, _
                    strMessageBody As String, _
                    Optional strAttachmentPaths As String, _
                    Optional strCC As String, _
                    Optional strBCC As String) As Boolean
 
    Dim objOutlook As Object
    Dim objNameSpace As Object
    Dim objExplorer As Object
    Dim blnSuccessful As Boolean
    Dim blnNewInstance As Boolean
 
    'Is an instance of Outlook already open that we can bind to?
    On Error Resume Next
    Set objOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0
 
    If objOutlook Is Nothing Then
 
        'Outlook isn't already running - create a new instance...
        Set objOutlook = CreateObject("Outlook.Application")
        blnNewInstance = True
        'We need to instantiate the Visual Basic environment... (messy)
        Set objNameSpace = objOutlook.GetNamespace("MAPI")
        Set objExplorer = objOutlook.Explorers.Add(objNameSpace.Folders(1), 0)
        objExplorer.CommandBars.FindControl(, 1695).Execute
 
        objExplorer.Close
 
        Set objNameSpace = Nothing
        Set objExplorer = Nothing
 
    End If
 
    blnSuccessful = objOutlook.FnSendMailSafe(strTo, strCC, strBCC, _
                                                strSubject, strMessageBody, _
                                                strAttachmentPaths)
 
    If blnNewInstance = True Then objOutlook.Quit
    Set objOutlook = Nothing
 
    SendEmail = blnSuccessful
 
End Function
Public Function ExportEmailFile(strOutputFile As String, _
            strObjectName As String, _
            strOutputType As String)
 
    Dim strFileType As String
 
    If Len(Dir(strOutputFile)) Then
        Kill strOutputFile
    End If
 
    If strOutputType = "Report" Then
        DoCmd.OutputTo acOutputReport, strObjectName, acFormatRTF, strOutputFile, False
    ElseIf strOutputType = "Query" Then
        DoCmd.OutputTo acOutputQuery, strObjectName, acFormatXLS, strOutputFile, False
    End If
 
End Function

And the Outlook code is:

Public Function FnSendMailSafe(strTo As String, _
                                strCC As String, _
                                strBCC As String, _
                                strSubject As String, _
                                strMessageBody As String, _
                                Optional strAttachments As String) As Boolean
On Error GoTo ErrorHandler:
    Dim MAPISession As Outlook.NameSpace
    Dim MAPIFolder As Outlook.MAPIFolder
    Dim MAPIMailItem As Outlook.MailItem
    Dim oRecipient As Outlook.Recipient
 
    Dim TempArray() As String
    Dim varArrayItem As Variant
    Dim strEmailAddress As String
    Dim strAttachmentPath As String
 
    Dim blnSuccessful As Boolean
    'Get the MAPI NameSpace object
    Set MAPISession = Application.Session
 
    If Not MAPISession Is Nothing Then
      'Logon to the MAPI session
      MAPISession.Logon , , True, False
      'Create a pointer to the Outbox folder
      Set MAPIFolder = MAPISession.GetDefaultFolder(olFolderOutbox)
      If Not MAPIFolder Is Nothing Then
        'Create a new mail item in the "Outbox" folder
        Set MAPIMailItem = MAPIFolder.Items.Add(olMailItem)
        If Not MAPIMailItem Is Nothing Then
 
          With MAPIMailItem
            'Create the recipients TO
                TempArray = Split(strTo, ";")
                For Each varArrayItem In TempArray
 
                    strEmailAddress = Trim(varArrayItem)
                    If Len(strEmailAddress) > 0 Then
                        Set oRecipient = .Recipients.Add(strEmailAddress)
                        oRecipient.Type = olTo
                        Set oRecipient = Nothing
                    End If
 
                Next varArrayItem
 
            'Create the recipients CC
                TempArray = Split(strCC, ";")
                For Each varArrayItem In TempArray
 
                    strEmailAddress = Trim(varArrayItem)
                    If Len(strEmailAddress) > 0 Then
                        Set oRecipient = .Recipients.Add(strEmailAddress)
                        oRecipient.Type = olCC
                        Set oRecipient = Nothing
                    End If
 
                Next varArrayItem
 
            'Create the recipients BCC
                TempArray = Split(strBCC, ";")
                For Each varArrayItem In TempArray
 
                    strEmailAddress = Trim(varArrayItem)
                    If Len(strEmailAddress) > 0 Then
                        Set oRecipient = .Recipients.Add(strEmailAddress)
                        oRecipient.Type = olBCC
                        Set oRecipient = Nothing
                    End If
 
                Next varArrayItem
 
            'Set the message SUBJECT
                .Subject = strSubject
 
            'Set the message BODY (HTML or plain text)
                If StrComp(Left(strMessageBody, 6), "<HTML>", _
                            vbTextCompare) = 0 Then
                    .HTMLBody = strMessageBody
                Else
                    .Body = strMessageBody
                End If
            'Add any specified attachments
                TempArray = Split(strAttachments, ";")
                For Each varArrayItem In TempArray
 
                    strAttachmentPath = Trim(varArrayItem)
                    If Len(strAttachmentPath) > 0 Then
                        .Attachments.Add strAttachmentPath
                    End If
 
                Next varArrayItem
            .Send 'The message will remain in the outbox if this fails
            Set MAPIMailItem = Nothing
 
          End With
        End If
        Set MAPIFolder = Nothing
 
      End If
      MAPISession.Logoff
 
    End If
 
    blnSuccessful = True
 
ExitRoutine:
    Set MAPISession = Nothing
    FnSendMailSafe = blnSuccessful
 
    Exit Function
 
ErrorHandler:
    Resume ExitRoutine
End Function

 

[FMa12]

Thank you very much, your help is much appreciated. I ll look into this and come back if I have further questions. Will probably have some...

Thanks!

 

[mdlueck]

As another suggestion, I utilize the command line email sending program called Blat. I have Access leveraging that to send emails.

http://www.blat.net/

 

[FMa12]

Hi everybody,

@CBrighton

I pasted both codes in VBA modules in Access and Outlook.

I also created query which now displays only the entries where an action is due today.

However, I don't understand how to combine these elements ...? Can you go into more details?

Thanks very much

 

[CBrighton]

The Access function is the one you would call, that in turn calls the Outlook function.

You would call it like this:

SendEmail("CBrighton@email.com","Test Subject","This is the message body","c:\Attachment.doc")

The attachemnt and the 2 remaining variables which I skipped (CC & BCC) are optional, only the address, subject & body are required.

:edit:

You can also use ExportEmailFile to save the query as an .xls before attaching it:

ExportEmailFile("c:\qryResults.xls","qryResults","Query")

 

[FMa12]

Thanks once again.


I called the function (trough a button, will later use autoexex macro) but everytime get the error 438: Object doesn't support this property or method.

The code looks like yours provided.

Also, when I click on debug, the below part of of the code you provided is marked yello.

blnSuccessful = objOutlook.FnSendMailSafe(strTo, strCC, strBCC, _
strSubject, strMessageBody, _
strAttachmentPaths)

Thanks for your help

 

[CBrighton]

To confirm, you use outlook, you pasted the provided code into outlook, you certified the code in outlook and when opening outlook you click the "enable macros" option?

Also, I assume your email account isn't blocked from sending .accdb files (I know that with my one I have to zip .mdb files as the security blocks it otherwise)?

 

[FMa12]

Ok, I think I didn't certify the code in outlook. As I am designing my database here at my workplace....I hope this will be possible. I m going to look at this.

 

[CBrighton]

You should be able to self-certify it using MS Office's SelfCert.exe, that's what I did.