Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Steven.Allman is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    118

    Email sending

    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?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Steven.Allman is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    118
    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.

  4. #4
    Steven.Allman is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2010
    Posts
    118
    Also, please note I have upgraded to 2007, so I have newer functionality, if that would make any of this easier

  5. #5
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Like pbaldy said above, you can use the SendObject method to send an e-mail message from within Access.

    Code:
    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.

  6. #6
    Steven.Allman is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2010
    Posts
    118
    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

  7. #7
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    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.

    Code:
      ' 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", ,
    Last edited by Rawb; 06-16-2010 at 07:41 AM. Reason: Highlighted some of the code

  8. #8
    Steven.Allman is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2010
    Posts
    118
    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...

  9. #9
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    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"):
    Code:
    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.
    Last edited by Rawb; 06-16-2010 at 08:08 AM. Reason: Whoops!

  10. #10
    Steven.Allman is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2010
    Posts
    118
    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

  11. #11
    Steven.Allman is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2010
    Posts
    118
    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

  12. #12
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Quote Originally Posted by Steven.Allman View Post
    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!

  13. #13
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    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?

  14. #14
    Steven.Allman is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2010
    Posts
    118
    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..

  15. #15
    Steven.Allman is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2010
    Posts
    118
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sending email
    By nashr1928 in forum Reports
    Replies: 8
    Last Post: 04-27-2010, 11:14 PM
  2. VB code for sending email through Option Group?
    By getdpt in forum Programming
    Replies: 0
    Last Post: 08-23-2009, 03:59 PM
  3. SENDING EMAIL MESSAGES DIRECTLY FROM ACCESS
    By Frenchos in forum Access
    Replies: 0
    Last Post: 07-20-2007, 12:51 AM
  4. Replies: 1
    Last Post: 03-09-2006, 01:50 PM
  5. Sending email via Outlook (and Exchange2003) from Access2003
    By Larry Elfenbein in forum Programming
    Replies: 0
    Last Post: 11-15-2005, 09:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums