Email sending

[Steven.Allman]

I want to add an email option to my database.
I have 190 companies, each with two to four contacts. Each contact information is in the table. contact 1, contact 2, contact 3, contact 4. Email is the only contact info so I dont need subtables or tables with relationships at all..
When i look at the company information page for Company A, I want to hit a button that opens outlook and creates a message with ALL email addresses in the information line.
Anyone have a good idea?

[pbaldy]

Well, the normalized structure would be a related table for contacts, but it doesn't sound like you want to hear that (but what will you do when somebody has 5?). In any case, you can use SendObject to start an email, and populate the "To" argument with the contacts. More info in VBA help.

[Steven.Allman]

I can do the related table, but this is a military application and there will never be more than 3 contacts for any one company. The information being mailed is of a sensitive, personal nature and its stored in a database I designed to get away from spreadsheets. It turned into a monster, and my bosses think I am a genius and seen someone say 'email from access.' After the round table of 'huurummphs' it was mandated I start adding that so they can report to their bosses with one easy click. I must maintain the appearance of genius, so I rely on you guys to help me out.

[Steven.Allman]

Also, please note I have upgraded to 2007, so I have newer functionality, if that would make any of this easier

[Rawb]

Like pbaldy said above, you can use the SendObject method to send an e-mail message from within Access.

DoCmd.SendObject acSentObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, ,

acSendObjectType is an Object from the database to attach to the message as an attachment. Can be left blank or have any of the following values: acSendDataAccessPage, acSendForm, acSendModule, acSendNoObject (if left blank, this is assumed), acSentQuery, acSendReport, or acSendTable.

ObjectName is the name of the database Object being attached to the message. Can only be left blank if acSendNoObject is used for the acSendObjectType.

OutputFormat is the file type to format the attachment as. Available options depend on your version of access but can include "HTML", "Microsoft Excel", "MS-DOS Text", "Rich Text Format", and "Snapshot Format". If left blank, the user will be asked for the file format.

To is the list of e-mail addresses to be listed in the "To:" field of the e-mail. Can be any text string or left blank.

Cc is the list of e-mail addresses to be listed in the "Cc:" field of the e-mail. Can be any text string or left blank.

Bcc is the list of e-mail addresses to be listed in the "Bcc:" field of the e-mail. Can be any text string or left blank.

MessageText is the text to include in the body of the e-mail message. Can be any text string or left blank.

E-mails sent will be formatted as Plain Text messages with attachments. They cannot be automatically formatted as Rich Text or HTML, however the user can change the format of the e-mail message manually before sending.

[Steven.Allman]

That is almost a direct copy paste from what most of the info I am seeing is. Problem is I want the to. field populated from a table lookup. The button will obviously be on a form for record 1234, so where can i find the coding to lookup table.list_address1, address2, address3, and of course address3 would be an iF_null blank etc.
I am not really asking anyone to write the code for me, unless they really really want to, but I do not do much code. I have messed with databases before, but just small amounts of data, and simple tasks. Now I am at the point where I am in way over my head. Its fun though. Nothing like seeing it work and screaming 'yeah' from the confines of my little cubicle though.. lol

[Rawb]

lol! Actually that is a copy/paste from Visual Studio (with some extra descriptions added below).

The Coding for something like that would be fairly simple but I'd need to know more about your Form before I can throw something together for you. In the meantime, here's a basic example that you might be able to use to figure it out yourself.

  ' Create a Recordset variable for our table. a Recordset is basically an Array
  ' that's already filled in with information from the table we link it to.
  Dim rstContactsList as Recordset

  ' Create a variable for our "To:" list.
  Dim strTo as String

  ' Populate our Recordset variable by telling it what table to link to. This
  ' can be done by either just entering a Table name (if you want access to
  ' the contents of the whole Table) or with a Query (used if the Table can
  ' contain a lot of information. This can be faster than using the whole
  ' Table and can help eliminate extraneous information.
  Set rstContactsList = CurrentDb().OpenRecordset("Table or Query", dbOpenDynaset)

  ' Initialize our "To:" variable to a blank string.
  strTo = ""

  ' If you're using a Query that will give you only the Contact you want,
  ' use the following code.

  ' If block to check whether or not an e-mail address is in each field in the
  ' Recordset. If it is, add it to strTo.
  If Not IsNull(rstContactsList("name of first contact field")) Then
    strTo = strTo + rstContactsList("name of first contact field")
  End If

  If Not IsNull(rstContactsList("name of second contact field")) Then
    strTo = strTo + rstContactsList("name of second contact field")
  End If

  If Not IsNull(rstContactsList("name of third contact field")) Then
    strTo = strTo + rstContactsList("name of third contact field")
  End If

  ' Attach the Report called rptReport as a Snapshot File to the e-mail and
  ' send it to our contact list with the subject "Subject" and the text
  ' "Message Body" in the e-mail's body.
  DoCmd.SendObject acSendReport, "rptReport", "Snapshot Format", strTo, , , "Subject", "Message body", ,

[Steven.Allman]

DoCmd.SendObject([ObjectType As AcSendobjectType = acSendReport], [Drug Positives], [PDF], [********@us.army.mil], [******@ng.army.mil], , [Dat Results FOR YOUR EYES ONLY], [The following SM has tested positive for . Please kick his ass out],,)

That is what I have right now and I am getting syntax errors. Your using quotes on yours but access uses the hard brackets correct?
And I honestly cant have you create the code, because I plan on making 4-5 different email buttons on different forms so I need to understand how so I can make this work like a champ...

[Rawb]

Nou'll want to use quotes everywhere that I do. Only use brackets when you reference the fields in the Query at the top of the code.

Try this for your SendObject line (assuming the Report you want to attach is called "Drug Positives"):

DoCmd.SendObject acSendReport, "Drug Positives", "PDF", "name@company.com", "anothername@company.com", , "Dat Results FOR YOUR EYES ONLY", "The following SM has tested positive for . Please kick his ass out",,)

' DoCmd.SendObject acSendReport, "Drug Positives", "PDF", name@company.com; anothername@company.com", , , "Dat Results FOR YOUR EYES ONLY", "The following SM has tested positive for . Please kick his ass out",,)

Also, I'm assuming you want the second e-mail address to show up in the "Cc:" field of the e-mail instead of having them both show up in "To:". If you want both of them, use the commented out line instead.

As for doing the coding yourself, no problem! It's kinda refreshing to see someone that wants to learn to do it themselves rather than just have stuff handed to them

P.S.
If you get an error about the file you're trying to attach, just remove the "PDF". When you run it, it'll ask you for the format: Write down what it calls the PDF document and use that exact string instead.

[Steven.Allman]

Still a syntax error.. There is a ) at the end and nothing anywhere else. I removed it, syntax error, put one up front, syntax error, left it alone, and then, no just kidding, syntax error.... uggg

[Steven.Allman]

FYI, I cant believe I just posted me and my counterparts emails.. Can you edit your post and star those out for me. LOL.. Sorry man

[Rawb]

FYI, I cant believe I just posted me and my counterparts emails.. Can you edit your post and star those out for me. LOL.. Sorry man

Done and done!

[Rawb]

Sorry, I should have removed that closing parentheses.

Does the error give you any more information or does it just say "Syntax error" and nothing else?

[Steven.Allman]

nothing else... what a pain in the ass..
and access 2007 VBA editor is even giving mouseover popup help on what you should type. I follow that to a T, and its a syntax error..

[Steven.Allman]

HOLY SHIT it works... I accidentally added a 0 at the end of the whole thing. Apparently there is one more argument that isnt being read or something.