Converting code from DoCmd.SendObject to SMTP

[sjl]

I currently am having email generated automatically via Task Scheduler, which opens a macro in my database, which in turn opens a form (code sits in the OnOpen event); the form's code determines if any emails need to be sent, which is done via Outlook. Works well...but not if server is re-booted (and my Outlook session is shutdown).

So, I have looked into having mail sent via SMTP...

I have attempted to wrap the code proposed by Lagbolt (http://www.access-programmers.co.uk/forums/showthread.php?t=136990) around what I currently am running (i.e. DoCmd.SendObject) ...


The code(below, revised from my DoCmd code) is currently running but nothing happens (i.e. email is not sent).


I am wondering.....


Can I insert the “With iMsg” into the “DoWhile” loop?
If so, is my code (below) in the right order?
Any help/suggestions greatly appreciated...

The way my previous DoCmd.SendObject code was working was the "If Then" clause evaluated each study’s due dates determining if they were 10 weeks out (e.g.), and then sent the specific manager of that project an email, if so. This code kept looping through all the studies until the EOF.



The code:

Private Sub Form_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database

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

Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmail_Notif_Copern")

Dim dtToday As Date
Dim dtCopPacketDue As Date
Dim strTo As String
Dim strEM As String
Dim strproject As String
Dim strCopernProtocol As String
Dim strstudymgr As String
Dim daysfromnowCOP10 As String

Dim Msg As String

On Error GoTo Cleanup

'configure message
With iConf
With .Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'cdosendusingport
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.niehs.nih.gov"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 30
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 0 'cdosendusingport
.Update
End With
End With

dtToday = Date 'todays date

Do While Not rst.EOF
dtCopPacketDue = rst!dtmCPacketDue
strTo = rst!strSMName
strEM = rst!strEmail_SM
strproject = rst!strBrochureName
strCopernProtocol = rst!strCopIntRefNum
strstudymgr = rst!strSMName

With iMsg
Set .Configuration = iConf
.To = "xxxx@nih.niehs.gov"
.From = "xxxx@nih.niehs.gov"
.subject = "Packet Due Date in +/- 10 weeks "
If DateDiff("w", dtToday, dtCopPacketDue, vbTuesday, vbFirstJan1) = 17 Then
daysfromnowCOP10 = DateDiff("d", dtToday, dtCopPacketDue) And .Textbody = "The Copernicus packet DUE DATE for " & strproject & _
" (protocol# " & strCopernProtocol & ") is: " & vbCrLf & vbCrLf & " " & dtCopPacketDue _
& vbCrLf & vbCrLf & "This is " & daysfromnowCOP10 & " from now."

.Send
End If
End With

rst.MoveNext
Loop

Cleanup:
rst.Close
CurrentDb.Close
Set rst = Nothing
Set db = Nothing

On Error GoTo 0

Set iMsg = Nothing
Set iConf = Nothing
Exit Sub

Handler:
Err.Raise Err, Err.Source
End Sub

 

[MarkK]

- What I suggest is do your data processing in a different routine, and pass the results of that processing to the smtp code. This'll keep the mail routine generic enough that you won't have to rewrite it when you need it for some future purpose.
- And the routine you're using to process your data can stay focussed on that alone.
- Just my 2c

 

[sjl]

-
What I suggest is do your data processing in a different routine, and pass the results of that processing to the smtp code. This'll keep the mail routine generic enough that you won't have to rewrite it when you need it for some future purpose.
- And the routine you're using to process your data can stay focussed on that alone.
- Just my 2c

Thanks for the input. I like the suggestion. However....

Can you elaborate on "in a different routine" and how I would pass the results to the SMTP code for each iteration of the data processing?
(each study has a different manager, project name, protocol number, etc...so I'm having a hard time visualizing how these fields can be stored and passed en masse to a subsequent routine.

thanks.

 

[MarkK]

Consider these two approaches...

Sub SendDataLadenMail
  [COLOR="Green"]'this routine lumps the data bit and the mail bit together[/COLOR]
  Dim DataThing
  Dim MailThing
  Do
    DataThing = GetYourDataTogether
    MailThing.Data = DataThing
    MailThing.Send
  Loop
End Sub

Sub SendDataLadenMail
  [COLOR="green"]'in this case, data is processed here[/COLOR]
  Dim DataThing
  Do
    DataThing = GetYourDataTogether
    [COLOR="green"]'here we pass the processed data to the mail routine[/COLOR]
    SendMail DataThing
  Loop
End Sub

Sub SendMail(SomeDataThing)
  [COLOR="green"]'this routine never needs a rewrite.  just throw data things at it.[/COLOR] 
  Dim MailThing
  MailThing.Data = SomeDataThing
  MailThing.Send
End Sub

- See how in the second case the mail routine is isolated from the data processing routine?
- Determine what the mail routine needs to send mail. Then write a data routine that produces exactly those parameters and calls the mail routine.
- Cheers