Automatic e-mail

[Kesh]

Ms-Access 2007

I have built up a report and I want to send it to users as attachment to an e-mail. The e-mail is to be sent automatically at 8:00 a.m daily on weekdays, I have written the following code which does open the e-mail and attaches the report but waits to be manually sent:
Can anybody please help? Thank you, Kesh

Sub cmdSendEmail_Click()
Dim sDocName As String
Dim sSubject As String
Dim sToName As String
Dim sMsgText As String

sDocName = "Call_List_to_Send"
sToName = "abcd.efg@xyz.com "
sSubject = "Stale Call List"
sMsgText = "Good Morning, Please find attached list of calls that are stale for more than 72 hours"

DoCmd.SendObject acReport, sDocName, acFormatPDF, sToName, , , sSubject, sMsgText, False

End Sub

 

[jatsold3]

I just went through a similar problem. One issue with trying to send emails via Access is the antivirus settings. Office 07 looks to see if the AV software is up to date, if so then the next step is your network security.

Will either of these be an issue? If so, then you would be stuck manually sending it out.

 

[McSwifty]

I had to do a similar thing.

I made a small Front End db with jsut enough in the way of forms etc. to run the email.
Then I made that FE open using taskscheduler in windows.
Just make the setting in the task scheduler close the program if it runs for 5 or 10 minutes.

Simple but it worked for me.

 

[Kesh]

Thank you jatsold3

Thank you McSwifty

Hi McSwifty,
Could you please elaborate on what the FE db does?
Thank you

Kesh

 

[McSwifty]

Hi Kesh,

When running a db in an environment where more than one person would sign into it.
It is advised to split the database into a front end and a back end.
The front end is the part with all of the queries and forms, reports etc.

The back end is where the tables and the relationships are.
This prevents corruption of the data when you have simultaneous users trying to change the same data. (record locking on edited record also prevents this somewhat)

If you are the only one to sign in and work on the data then a single file should be ok. But remember there is a file size limit with access as well depending on the version.
I can't recall those limitations now, but I have a reasonably large access 2007 db running on a shared server drive on my worksite, this can be accessed boy over 80 staff, but max users I have seen on at any one time it only 6-9. My file sizes after 5 years of this running are only 23mb for the front end and the back end is around 13mb.

If you need info on splitting the database - if you need to - check out the search tool in this forum, it works pretty good. I have been looking for an answer to my question and at times have found a shortcut to do something I wanted to for another later search.

 

[jatsold3]

Kesh not sure if this will still be helpful but this is what I have to work in my database:

First add reference [FONT=&quot]Microsoft Outlook 12.0 Object Library[/FONT]

       Dim objOL As Outlook.Application
       Dim objNS As Outlook.NameSpace
       Dim objMail As Outlook.MailItem
       Dim blnWeOpenedOutlook As Boolean
       Dim strBody, strMsg As String
       Dim intRes As Integer

Now we need to make sure there is an Outlook session

      Set objOL = GetObject(, "Outlook.Application")
      If objOL Is Nothing Then
          Set objOL = CreateObject("Outlook.Application")
          Set objNS = objOL.GetNamespace("MAPI")
          objNS.Logon
          blnWeOpenedOutlook = True
      End If

Here is where the message is created and sent

    Set objMail = objOL.CreateItem(olMailItem)
      With objMail
          .BodyFormat = olFormatPlain
          .Display
          .GetInspector.WindowState = olMinimized
          .Subject = "[B][COLOR=red]Enter a subject[/COLOR][/B]"
          .To = "[B][COLOR=red] Enter the Email Address[/COLOR][/B]"
          .CC = "[B][COLOR=red] Enter the carbon copy email address[/COLOR][/B] "
          .Attachments.Add [B][COLOR=red]"(file location and name)"[/COLOR][/B]
          strBody = .Body
          If Err.Number = 0 Then
              .Body = "[B][COLOR=red]Enter your email message[/COLOR][/B] " & Now() & vbCrLf & strBody
          Else
              .Body = "[B][COLOR=red] Enter your email message[/COLOR][/B] " & Now()
              Err.Clear
          End If
          .Send

On error

          If Err.Number <> 0 Then
              strMsg = "Outlook cannot send the message unless you click Allow on the" & _
                       "security prompt. Do you want to try again?"
              intRes = MsgBox(strMsg, vbQuestion + vbYesNo, "SendOutlookMessage")
              If intRes = vbYes Then
                  Err.Clear
                  .Send
              End If
          End If
      End With

Close Outlook session used in Access

      If blnWeOpenedOutlook Then
          objOL.Quit
      End If
      
      Set objMail = Nothing
      Set objNS = Nothing
      Set objOL = Nothing

In order to get this to be sent every morning I have a .BAT file open my database which is set to run code once opened. Hopefully this helps.