Batching e-mails 1

[scottian]

I have a database which has been set up to handle IT problems which are e-mailed to a service desk. At the moent i have the e-mail function set up send an e-mail for each problem. However the service desk are not keen on individual e-mails and want no more than one e-mail per hour.
Is there a way to queue the mails an then send a batched e-mail at a set time.

"My God! It's full of stars...

 

[Remou]

I am pretty sure you can do this with Outlook, but it would take a bit of coding.

 

[idbr]

Hi scottian,

Could you just output the list to a spreadsheet and email that? You could run this on a timer interval so it would run automatically.

Have examples you could work from if it's an appropriate way to go...

Iain

 

[scottian]

If i do use the excel method. is it possible to automate it so that the file is placed in a folder and an e-mail is triggered which picks up the file?

"My God! It's full of stars...

 

[idbr]

It is, you'd need to run your output process from a form timer event, so this method relies on you always having a form open that stays open. Perhaps you could create a hidden form that opens with the database and closes on shutdown?

For the form timer event you'll need:

Private Sub Form_Timer()

Call My_Output_Process

End Sub

Set the timer interval property for the form to however often you want it to run. 1000 units = 1 second, so for every 30 minutes this would be 30 x 60 x 1000 = 1800000.

I'd suggest a yes/no flag field in your table to tell you which records need to be exported. Just update this field when you run the routine.

Are you familiar with how to output the spreadsheet list and attach it to an email? Can help with that if not...

Iain

 

[scottian]

Ive automated the output of spreadsheets in the past, but Ive never done the email attach part.

any help would be appreciated.

"My God! It's full of stars...

 

[idbr]

Emailing is nice and easy, try adapting this code to your own situation...

Dim appOLK          As Outlook.Application
Dim olkMsg          As Outlook.MailItem
Dim strFileName     As String 'Your spreadsheet FULL PATH

Set appOLK = New Outlook.Application
Set olkMsg = appOLK.CreateItem(olMailItem)

With olkMsg

'Set up the message
.Subject = "Your Subject"
.Body = "Your Body Text"
.Recipients.Add "Your@Recipient"

'Attach the file
.Attachments.Add strFileName

'Send the message
.Send

End With

Set olkMsg = Nothing
Set appOLK = Nothing

HTH, Iain

 

[scottian]

excellent,



"My God! It's full of stars...

 

[Remou]

In case any other reader is interested:

Sub CheckMail()
Dim olApp As Outlook.Application
Dim olNS As NameSpace
Dim olRecItems As Outlook.MAPIFolder
Dim olFilterRecItems As Items
Dim olNewMail As Outlook.MailItem
Dim strFilter As String
Dim dteLastCheck As Date
Dim dteThisCheck As Date
Dim strNewMessage As String
Dim i

'Dates for testing
dteLastCheck = DateAdd("d", -30, Now())
dteThisCheck = Now()

'Outlook Application and Namespace
Set olApp = CreateObject("Outlook.Application")
Set olNS = olApp.GetNamespace("MAPI")

'Outlook Inbox
Set olRecItems = olNS.GetDefaultFolder(olFolderInbox)

'Filter for Restrict. Note that there are no seconds
'and that the date is a string.
strFilter = "[ReceivedTime] > " _
          & Chr(34) & Format(dteLastCheck, "mm/dd/yyyy hh:nn") & Chr(34) _
          & " AND [ReceivedTime] < " _
          & Chr(34) & Format(dteThisCheck, "mm/dd/yyyy hh:nn") & Chr(34)

'Restrict the mails in the Inbox by the filter
Set olFilterRecItems = olRecItems.Items.Restrict(strFilter)

'Build a new mail body from filtered mails
strNewMessage = "Number of mails: " & olFilterRecItems.count & vbCrLf
For i = 1 To olFilterRecItems.count
    strNewMessage = strNewMessage & olFilterRecItems(i).Body & vbCrLf
Next

'New mail
Set olNewMail = olApp.CreateItem(olMailItem)

With olNewMail
    .To = "a@b.c"
    .Subject = "Emails"
    .Body = strNewMessage
    .Display
End With

'Reminder
dteLastCheck = dteThisCheck
End Sub