Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access VBA to send email through Outlook

    Hi all

    I've managed to create some VBA to send an email through Access however the button only works when you click it once so if you want to send more than one email the only way you can do it is to close the form and reopen it?

    Its been suggested that I need to enter 'Set StrEmail = Nothing' somewhere but wherever I try to put it, it doesn't work!

    The form allows you to select whether you want to or BCC and the subject, and then it allows you to select the recipients from a drop down menu which show in a subform table.

    I'm relatively new to this so any help would be appreciated.

    This is a the code;
    Code:
    Private Sub Command116_Click()
    Dim Mess_body As String
    Dim strEMail As String
    Dim oOutlook As Object
    Dim oMail As Object
    Dim strAddr As String
    Dim rst As DAO.Recordset
     
    Set oOutlook = CreateObject("Outlook.Application")
    Set oMail = oOutlook.CreateItem(0)
    
    'Retrieve all E-Mail Addressess in tblEMailAddress
    Set rstEMail = [Current Governor Details Query subform].Form.RecordsetClone
     'Set MyDB = CurrentDb
     'MyDB.OpenRecordset("Select * From MyEMailAddresses where[Email]'[Forms]![Current Governor Details Query subform]![EMail].", dbOpenSnapshot, dbOpenForwardOnly)
    With rstEMail
      Do While Not .EOF
        'Build the Recipients String
        strEMail = strEMail & ![EMail] & ";"
         .MoveNext
      Loop
    End With
    '--------------------------------------------------
    If IsNull(Me.Mess_Subject) Then
     MsgBox "Oops! You've forgotten to enter a subject for the email", vbOKOnly
    Else
     
      If Me.List41 = "To" Then
      With oMail
      .To = Left$(strEMail, Len(strEMail) - 1)
        .Subject = Me.Mess_Subject
       .Display
       End With
       
       Else
       If Me.List41 = "BCC" Then
       With oMail
       .BCC = Left$(strEMail, Len(strEMail) - 1)
       .Subject = Me.Mess_Subject
       .Display
       
      ' If Me.Option28 = True Then
    'With oMail
    '.To = Left$(strEMail, Len(strEMail) - 1)
        '.subject = Me.Mess_Subject
       ' .Display
    'End With
     ' Else
       ' If Me.Option32 = True Then
        'With oMail
        
            '.BCC = Left$(strEMail, Len(strEMail) - 1)
              '.subject = Me.Mess_Subject
       ' .Display
    End With
     Else
        MsgBox "Oops! You need to select To or BCC to create an email", vbOKOnly
    End If
    End If
    End If
     
    Set oMail = Nothing
    Set oOutlook = Nothing
    rstEMail.Close
    Set rstEMail = Nothing
    
    End Sub
    Last edited by WendellB; 2015-01-19 at 10:13. Reason: Put code in code box

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Hi Kelly. I edited your message to put your VBA procedure into a code window so it's a bit easier to read and has indents, etc. You can do that by using [ code ] and [ /code ] tags without the spaces.

    I don't see any obvious reason why you shouldn't be able to click the button on the subform again and have it work, but it appears to me that the statement
    Code:
    strEMail = strEMail & ![EMail]
    should have something like Me![EMail] or a recordset name, possibly rstEMail![EMail]. Is there a specific reason why you are using a subform for this function? We typically put this sort of code into a standalone module (as opposed to being attached to a form or subform) so we can call it from multiple places. Then the button click would have a small routine passing the parameters to it.

    Automating Outlook from Access can be a powerful tool when you have this kind of need, and we've used it to create individual customized emails with specific attachments, but in some cases it is just as easy to create the email in Outlook using some of the program-ability tools there. I suppose you've looked at that possibility, but I'm a big proponent of using the best tool for the job, and Outlook has some pretty good tools for managing lists of email addresses.
    Last edited by WendellB; 2015-01-19 at 10:15.
    Wendell

  3. #3
    New Lounger
    Join Date
    May 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Doc1.pdfHI Wendell

    Thanks for your reply and for editing my code! Ive only posted on here once before!

    Basically I have a form and on this form is a couple of combo boxes and a table. The user is able to pick options from the combo boxes which will filter the table. The users can then see who the email will send to or if there is even an email address there!

    The VBA then gets the email addresses from the table and populates the email in access with the addresses.
    There are about 11,00 people in the database but we don't need to email them all (not that outlook would let you send to more than 500 anyway).

    I tried putting in your suggestions but neither work. I think the problem lies when you change the filters this is what causes the problem, like it needs to requery or something?

    I have attached a file (I think) of what my email screen looks like.

    Thanks

    Kelly
    Attached Files Attached Files
    Last edited by kellyk87; 2015-01-19 at 10:55.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    If no one has welcomed you to the Windows Secrets Lounge, let me be the first to do so. I did take a quick look at your prior thread, and it seems you have undertaken an ambitious task for a newcomer to VBA, and presumably to Access. I also noted that you are from the UK (a place with fond memories from when we lived there) and wanted to point out that there is a very active Access community based there. User groups are a great way to network and sort out issues, as well as learn new things. You can find them at http://www.ukaug.co.uk/.

    It appears that you may not need to use a subform for the display of the list of "contacts" - it could be done as the main part of the form if you are willing to put the buttons, filters and other text boxes in either the form header or footer. The reason is that subforms complicate the VBA statements considerably - and since your code belongs to the main form, you generally need to reference the control within the subform. From what I can see, you are really only using the subform to display the records that are available, and suggesting they fill in blank ones. But one thing I don't see is a declaraton for rstEmail, and that appears to be what you are using to get the email addresses. To help much more with it, I think we would need to see a simple version without any real data. Once you've shrunken it to a minimal set for testing purposes (include only a few dummy records), do a Compact and Repair, and then zip it and attach the zip file to a post.
    Wendell

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •