Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2007
    Posts
    17

    Unanswered: Send an email address from Access to Outlook's TO line

    I have read a lot of stuff about sending emails from Access, etc... but that is not what I want to do...

    All I want to do is this:

    I have a table that keeps peoples info in it like: name, phone, email, etc.

    I want to loop through the records and get the email addresses and send them to Outlook:

    Code:
    //Jasen Code, not actual code
    
    total =  total number of records from table 'Reservists';
    
    i=1;
    
    for (i<=total)
    {
    
    email = email + "SELECT [E-mail] FROM Reservists WHERE RECORD = i";
    
    email = email + ";" ; // This way each email address is seperated by a semicolon
    
    i++;
    
    }
    
    Open Outlook and Export email to the TO line.
    All from clicking ONE button on my control panel.

    Possible?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Take a look into the SendObject method
    Code:
    DoCmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)
    Remember, you can set ObjectType to acSendNoObject and EditMessage to True to open the message in your default email application.

    Genius, huh?
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2007
    Posts
    17
    I'm sorry, but I am VBA retarded, how exactly would I do this? I understand the concept and could easily do it PHP/MySQL but Access/VBA is beyond my knowledge still.

    Thanks for the help so far.

    Jasen

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Open your form in design view, right click the button and build event. Then go to "code builder"

    Then start playing with the command.
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think you are after this kind of code:

    Code:
    Dim rs as Recordset
    Dim vRecipientList as String
    Dim vMsg as String
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM CONTACTLIST")
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        Do
            vRecipientList = vRecipientList & rs!EmailAddress & ";"
            rs.MoveNext
        Loop Until rs.EOF
        vMsg = "Hello" & vbCrLf & vbCrLf & _
                "Message text" & vbCrLf & vbCrLf & _
                "Kind regards," & vbCrLf & vbCrLf & vbCrLf & "Signoff" 
        DoCmd.SendObject acSendReport, "ReportName", acFormatSNP, vRecipientList, , , "Subject", vMsg
    Else
        MsgBox "No contacts."
    End If
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Dec 2007
    Posts
    17
    Here is what I put in:

    Code:
    Sub SendMail()
    
    Dim rs As Recordset
    Dim vRecipientList As String
    Dim vMsg As String
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Reservists")
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        Do
            vRecipientList = vRecipientList & rs!Email & ";"
            rs.MoveNext
        Loop Until rs.EOF
        vMsg = "Hello" & vbCrLf & vbCrLf & _
                "Message text" & vbCrLf & vbCrLf & _
                "Kind regards," & vbCrLf & vbCrLf & vbCrLf & "Signoff"
        'DoCmd.SendObject acSendReport, , acFormatSNP, vRecipientList, , , "Subject", vMsg
        'DoCmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)
        'DoCmd.SendObject acSendNoObject, acSendNoObject, acSendNoObject, vRecipientList, acSendNoObject, acSendNoObject, acSendNoObject, acSendNoObject, True, acSendNoObject
        DoCmd.SendObject acSendNoObject, , , , vRecipientList, , , , , True
        
        
        
    Else
        MsgBox "No contacts."
    End If
    
    End Sub
    And I get: Run Time Error 2995, Unknown Message Recipients, the message was not sent.

    Could this because I have blank email addresses in the database?

  7. #7
    Join Date
    Dec 2007
    Posts
    17
    Hmm... ok, now I edited the code to be this:

    Code:
    Option Compare Database
    
    Sub SendMail()
    
    Dim rs As Recordset
    Dim vRecipientList As String
    Dim vMsg As String
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Reservists")
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        Do
            If rs!Email = Not Null Then
                vRecipientList = vRecipientList & rs!Email & ";"
                rs.MoveNext
            Else
                rs.MoveNext
            End If
            
        Loop Until rs.EOF
        vMsg = "Hello" & vbCrLf & vbCrLf & _
                "Message text" & vbCrLf & vbCrLf & _
                "Kind regards," & vbCrLf & vbCrLf & vbCrLf & "Signoff"
        'DoCmd.SendObject acSendReport, , acFormatSNP, vRecipientList, , , "Subject", vMsg
        'DoCmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)
        'DoCmd.SendObject acSendNoObject, acSendNoObject, acSendNoObject, vRecipientList, acSendNoObject, acSendNoObject, acSendNoObject, acSendNoObject, True, acSendNoObject
        DoCmd.SendObject acSendNoObject, , , , vRecipientList, , , , , True
        
        
        
    Else
        MsgBox "No contacts."
    End If
    
    End Sub
    I got rid of the runtime error but now I have no email addresses in the TO line in outlook!

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The SendObject line has too many commas, displacing its arguments.

    Try remming your SendObject and replace it with this:
    DoCmd.SendObject acSendNoObject, , ,vRecipientList, , ,"Subject" , vMsg, True
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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