Access 2000 Email Send code or macro?

[chayton6]

Hello all -
First, thank you for this forum. Second, please forgive me - I know not what I do :) I've searched previous posts, and I can't quite come up with the answer.

I have Access 2000. I need to come up with some way to email our vendors with a listing of their invoices that we've paid over the previous week. I currently have two tables - both are linked to external tables so that they're automatically updated each week. So there's no need for me to query for dates.

The first table is called TBL Contact Info and contains the following fields: email (primary email address), contact (this is a name), fax (number if we don't have email information), cc2 (some vendors prefer their reports to be cc'd to another of their associates), and VID (vendor ID number - linked relationship with the other table.)

The second table is called Remits and contains the following fields: VID (linked to other table), vendor_name, payment_number, invoice_number, invoice_date, invoice_amount, and payment_amount.

I also have two queries called QRY EMAIL LIST (which returns only those vendor contact info records that have an email address) and QRY FAX LIST (which returns only those vendor contact info records with a blank email and a fax number).

My email program is Outlook 2000.

My question is, what is the best macro or vba code to prepare and send emails to each vendor listing only their invoice payment information?
The big issue for me is that the list of vendors will change - some will get paid one week, while another week another set may get paid - that's why it's important to pull from the email list query because if a vendor doesn't have payments for that week I don't want to send them an email.

Thank you in advance for any help -
Elle
:hi: : pray2: :help :dunno

Update: 4/4/06 1:32 pm
Can someone please help? If this is answered in another thread, could you please direct me to it?

I've tried a few things, including merging to Word, but I'm unable to generate separate email reports for each vendor that way (that I've found). Thanks again -

[OBP]

This is fairly straightforward in VBA, and the main point is to build a string containing the invoice data for the main body of the email. I have some code that you could adapt or if you are not that confident with VBA I can do it for you.

[chayton6]

Hmmm, I have zero confidence in my VBA skills. They're nonexistant. But I *am* trying to learn. If you have code that I may adapt, please do send it on. And I may have to take you up on the next offer if I can't figure it out soon. Thank you :)

[OBP]

This is the actual emailing part of the VBA.

DoCmd.SendObject , "", "", mymaillist, mymaillistcc, , "Subject info", mymailcontent, False
where the mymailcontent contains the string of data created from your records, mymaillist is a list of recipients and mymaillistcc is a list of cc recipients.

[GaryB]

Here is non-coder's ( Me ) way of getting around this. I do a lot of emailing from Access and the best results I have found is to create a report, print the report as an Adobe PDF file. You will have to setup a pdf printer in your printer settings in Windows and have a full copy of Acrobat. Set your pdf print settings to automatically open the pdf in Acrobat. When the report is opened you can select send mail and it will give you access to Outlook and you can email the pdf to your Vendors. It is a simple solution to this problem and it also lets you retain a record of what you sent.

GaryB

[chayton6]

Hi all -

Well, I actually got it to work and now have my macro call the little module. It's not that big, but I wouldnt have had any clue where to start. I got the answer on another board (I'm not sure if it's allowed to post what board here?) but this is what I did.

I opened a blank module and pasted this:

Option Compare Database
'------------------------------------------------------------
' pullbyvendor
'
'------------------------------------------------------------
Function pullbyvendor()
On Error GoTo pullbyvendor_Err

Dim strToWhom As String
strToWhom = Reports!Email_Report!email2

DoCmd.SendObject acReport, "Email_Report", "SnapshotFormat(*.snp)", _
strToWhom, "hell@sc.m.com", "", " Report", "", True, ""

pullbyvendor_Exit:
Exit Function
pullbyvendor_Err:
MsgBox Error$
Resume pullbyvendor_Exit
End Function


What this does is create a function (code) called pullbyvendor that will create a screenshot of my report, attach it to an email, and send it to the email address in the 'email2' control of that report. Yes, the email addresses in this code (for carbon copies of the email to be sent to) are faked out so that our real email addies don't appear.

Then, in my macro (stop cringing) I used 'runcode' and the name of the code is pullbyvendor ()

The macro does a few things that allows me to do what I wanted it to do - which was basically email each vendor a report.

1 - I have a switchboard form with three options - the first updates the database. Updating is important for me because of the way I have my table set up.
- Updates/refreshes linked tables from the external excel spreadsheets that my primary user has control over.
- Deletes my 'email control table' - which keeps track of reports sent during the macro
- Runs a query to create a table which lists each vendor's ID code and email address once, and adds a 'sent' tracking colum. I call this table TBL email loop primary (I'm for simple easy to understand names) - This table is important!

Ok, the next command button on my switchboard allows me to print my 'to be faxed' reports - which are for those vendors who don't have an email address on file (slack behind the times jokers). Once this report prints (and has a page break between vendors so no one gets anyone elses info) a support assistant faxes them out according to the fax numbers and contact info provided at the top of each page. Handy.

The third button runs my 'send emails' macro. (Stop cringing darn it!) - It opens a form which lists the vendor IDs of those vendors who need to receive a report this week. It has a nifty little code that doesn't allow the drop down combo box to be blank. I love that. When the user clicks 'submit' the loop begins.

First, a query is run that places that vendor ID in a table (TBL active vendor) all by itself. This is important so that the loop can continue without having any 'failure to lock table' errors. Fun. The query which the report is pulled from then combines that vendor ID with all the pertinent info needed to create the report. Then, the report itself comes up in print preview mode (so that I can see the darned thing and make sure it's working properly).

Then, the pullbyvendor function kicks in - attaching the report to an email addressed to the email addressed int he email2 control on the report. Once the email is sent, the macro continues on to update the email control table to let me know that vendor has already received his report. Still fun.

Then a query runs to pull all the vendors from that table where the 'sent' column is null. The vendor drop down combo box is populated from this query. (I forgot to mention, this query runs for the first time in my 'update database' macro for the switchboard command button). So, when that combobox next appears, that vendor is no longer an option.

Then, the combobox appears, the macro selects the next record, and through the magic of macros (Ok, rock throwing is prohibited) it continues the loop by itself until that combo box is 'null' - at which point the macro breaks off to a msg box and beep and 'stop all macros' path.

Another thing I learned that was super cool - once you create a macro, you can 'save as' a module to get all that nifty VBA code that you want to learn anyway. Then go in and clean it up (if you're not afraid of further screwing it up - which I've been known to do). So, this is how I'm learning VBA and will eventually have my macros done in VBA instead. But, until then, I thought this explanation might help another complete and total VBA illiterate like me figure out how to do this.

Elle

Post edited 10-Apr-06 by geekgirlau. Reason: Insert line breaks in code

[geekgirlau]

Hi Chayton,

I've edited your post to insert a line break in the code - it makes it easier for others to read if they don't have to scroll to the right.

I'm also waiting for a KB entry to be approved that I think will do exactly what you want - I'm sure this will be available shortly (no pressure MD!). In the meantime, have a look at the attachment and see if this helps.