Emailing using Groupwise from Access

[Westeral]

I have a database that I create spreadsheets for export to a central office.
I want to automate the emailing of these generated spreadsheets to that
office using our email client, Groupwise. I went to the MVPS site and found
modules that allow me to do this but only if I name the files in the module.
Each month they will have a different name (with the name of the month in the
file name). I want to email all xls files in the directory I place them in
and them delete them after emailing. The code from the module follows and it
works well when varattach(0 to 2) is used and I name the files. Any way to
send all xls files and then delete them?

Sub RunDemo()
'this is a sample usage routine
On Error GoTo Err_Handler
Dim strTemp As String
Dim varAttach(0) As Variant
Dim strRecTo(1, 0) As String
Dim lngCount As Long
Dim varProxies As Variant
Dim cGW As GW

varAttach(0) = "c:\temp\*.xls"
'varAttach(1) = "c:\temp\Grand Rapids-Supervisors-May.xls"
'varAttach(2) = "c:\temp\Grand Rapids-Agents-May.xls"

strRecTo(0, 0) = "(e-mail address removed)"
strRecTo(1, 0) = "Full Name 1"

Set cGW = New GW
With cGW
.Login
.BodyText = "This is a test"
.Subject = "Monthly Case Review Spreadsheets"
.RecTo = strRecTo
.FileAttachments = varAttach
.FromText = ""
.Priority = "High"
strTemp = .CreateMessage
.ResolveRecipients strTemp
If IsArray(.NonResolved) Then MsgBox "Some unresolved recipients."
.SendMessage strTemp
.DeleteMessage strTemp, True
End With
'Kill varAttach(0)
'Kill varAttach(1)
'Kill varAttach(2)
Exit_Here:
Set cGW = Nothing
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
Resume Exit_Here

End Sub

 

[Bob Quintal]

I have a database that I create spreadsheets for export to a
central office. I want to automate the emailing of these
generated spreadsheets to that office using our email client,
Groupwise. I went to the MVPS site and found modules that allow
me to do this but only if I name the files in the module. Each
month they will have a different name (with the name of the month
in the file name). I want to email all xls files in the directory
I place them in and them delete them after emailing. The code
from the module follows and it works well when varattach(0 to 2)
is used and I name the files. Any way to send all xls files and
then delete them?

What you need to do is use the vba Dir() function inside a Do Loop to
read the directory and populate the varAttach array.
After sending the email, use another loop to delete each file which
appears in the varattach array.

 

[Westeral]

Thanks for your help. Should do the trick.