Automate Groupwise email w/ .pdff attachments via VBA.

[smith82369]

I have created a module in MS Access 97 that creates separate .pdf files by sending a Report to Acrobat Distiller for each customer invoice and names each file based upon data drawn from the source table in Access. The source table (and report actually) contain the email address for each account.Is there a means to include emailing the .pdf file within the same module immediately after it is created and use the data already available in Access to obtain the email address?

Between 1) Using Groupwise vs Outlook and 2) .pdf not being supported directly within Access; I've been banging my head against the wall trying to figure out how to accomplish both goals.

Thx for any assistance.

 

[Tiglet]

I've got some code that I wrote for Groupwise 5.5 running out of Excel, it sends different attachments to different recipients with cc recipients, subject and formatted personally addressed body text. It is probably very badly written as I'm no expert and I wouldn't have a clue how to tell you to put it into Access or how to send the files immediately on creation (Mine does them all in one go after being created)

There are probably lots of other people who will be able to give you better advice but if you want it let me know.

 

[smith82369]

Thx Tiglet....

Yes please forward the code to me....slowly I am finding different bits and pieces that are getting me closer to what I want to do.

Actually it is not imperative that the file be emailed immediately upon creation, I was just thinking it would be easier to do then if all of the data was contained in an open report that could be easily extracted.

my email is Robert_l.Smith@aa.com.

Thx. Rgds.

 

[x68]

smith82369 - would it be possible if i could get this code from you? i am also trying to create pdf files for creating commission statements for each sales person based upon data drawn from an Access source table. this would really help!

thx in advance,

tbruce@yahoo.com

 

[Tiglet]

Hi,

I thought I'd put it up on the site so that it's available to all who want it.
I used this to send out a whole bunch of Excel spreadsheets held in one directory all listed on Excel. (sent out 300 emails to varied recipients with varied subject, message and attachments in under 2 minutes.)
The macro is set up to handle up to two recipients and one Cc recipient, although again, that can be changed to suit your requirements. I had a system of file names which used two names:- strThisCC and strSheetName, you may want to ditch one!
Don't forget to add in the references to Groupwise using Tools References off the VBA menu.
Best of luck

Tiglet


Dim strThisCCAs String, strSheetName As String
Dim RecipientAs String, subject As String
Dim RecipientTwo As String
Dim Cc_recipient As String
Dim ChristianName As String

Sub Directory_List()

Dim myRow As Integer
Dim myFile As String

'This Sub lists out all of the .xls files in the current
'directory. (You can change the directory by using the
'Chdir command and change the file extensions to .pdf in
'your code)

myRow = 1
myFile = Dir("*.xls&quot
Do Until myFile = ""
Cells(myRow, 1) = myFile
myRow = myRow + 1
myFile = Dir
Loop

End Sub

Sub Auto_email()
'
'Uses info on spreadsheet to send attachments to email recipients with subject line.

Do While True
strThisCC = ActiveCell.Value
If strThisCC = "" Then Exit Sub
ActiveCell.Offset(0, 1).Activate
'move to next column
Recipient = ActiveCell.Value
ActiveCell.Offset(0, 1).Activate
'move to next column
RecipientTwo = ActiveCell.Value
ActiveCell.Offset(0, 1).Activate
'move to next column
Cc_recipient = ActiveCell.Value
ActiveCell.Offset(0, 1).Activate
'move to next column
subject = ActiveCell.Value
ActiveCell.Offset(0, 1).Activate
'move to next column
ChristianName = ActiveCell.Value
strSheetName = strThisCC & "_Bud_04.xls"
'sets full file name
Call Email_Send
ActiveCell.Offset(1, -5).Activate
'takes you back to the next file name in list
Loop

End Sub

Function Email_Send()

Dim GWApp As Object
Dim gWAccount As Account
'Open a GroupWare session Object
Set GWApp = CreateObject ("NovellGroupWareSession&quot
'login to GW
'Login using () will login to the open account or run the
'login script if none was opened.
Set gWAccount = GWApp.Login()
'Create a new message in the Mailbox
Set gwmessage = gWAccount.MailBox.Messages.Add
gwmessage.BodyText = ChristianName & Chr(10) & Chr(10) "Enter Body Text Here"
'The above '& Chr(10)' etc is ASCII formatting for new lines
'etc
gwmessage.subject = subject
gwmessage.Recipients.AddByDisplayName Recipient
If RecipientTwo <> &quot;&quot; Then gwmessage.Recipients.AddByDisplayName RecipientTwo
If Cc_recipient <> &quot;&quot; Then gwmessage.Recipients.AddByDisplayName Cc_recipient, 1
gwmessage.Attachments.Add (&quot;C:\Enter name of directory here\&quot & strSheetName
'Add an open workbook to the attachments
gwmessage.send
'Send out

End Function

 

[Tiglet]

Ooops forgot to put in the following:-
Set each object variable to Nothing

eg Set gWAccount = Nothing

so that memory gets released after execution.

Tiglet

 

[smith82369]

Thx Tiglet for posting the code....I am probably incredibly ignorant on this but I have set up my process and everything looks good except I do not know where the &quot;Account&quot; type in the &quot;Dim gWAccount as Account&quot;. My code will go thru the entire process until it gets to the function and then give me a type error on this line.
I am assuming that &quot;Account&quot; is a user type that must be defined but where does that definition come from?

Thx. Rgds.

 

[Tiglet]

Things to try!

Take out the line Dim gwAccount as Account, it doesn't actually have to be defined - that might solve the problem.

Have a read of this link off Novell's site

http://developer.novell.com/ndk/doc/gwobjapi/index.html

Tiglet

 

[smith82369]

Thx. Tiglet....

I finally got time to work on this and simply by changing the &quot;Account&quot; to &quot;Object&quot; it worked perfectly. I had tried the same thing with another set of code but there had to be something else wrong.

Thx again for all your help and I hope it helps everyone else who was trying to do something similar.

 

[Tiglet]

No worries,

Glad it worked

Tiglet

[green]Duct tape is like the force; it has a light side & a dark side, and it holds the universe together. [/green]