Access 2003 MailShot. (1 Viewer)

[djshrew]

I am trying to add a feature so my client can mail all the contacts, but i am drawing blanks. the mail single client is fine, but i would like to mail all. dont need to attach anything just add all the mail contact to the to: line in word or outlook.

Thanks

Den

 

[Rabbie]

Sorry - What is your question?

 

[djshrew]

the button mail all, i would like it to mail all the contacts in the database.

but dont want it to attatch anything simple added them to the To: line in the email so i can attatch items and compose the email manualy without having to add all the contacts into outlooks address book.

In short i would like to mail all customer form the table:Contacts under the field:EmailName. (with no conditions attached.) Simple just all them all to an email address line, so i can compose the mail manualy.

 

[djshrew]

does anyone know how i can mail all contacts in this database?

 

[JANR]

You need to open a recordset and loop through the email adresses and add to Outlook recipients.

something like this:

Function SendMassMail()
Dim rs As DAO.Recordset
Dim oApp As Object
Dim oMail As Object
Dim oRecipients As Variant
Const olMailItem = 0

On Error GoTo ErrorHandel

Set rs = CurrentDb.OpenRecordset("Select [COLOR="Red"]CustEmail[/COLOR] From [COLOR="red"]tblCustomer[/COLOR]")

If rs.RecordCount > 0 Then
    rs.MoveFirst
    On Error Resume Next
        Set oApp = GetObject(, "Outlook.Application")
            If Err.Number <> 0 Then   ' Outlook is not open
                Err.Clear
                Set oApp = CreateObject("Outlook.Application")
            End If
    On Error GoTo ErrorHandel
    
    Do Until rs.EOF
            oRecipients = oRecipients & (rs.Fields("[COLOR="Red"]CustEmail[/COLOR]")) & ";"
            rs.MoveNext
    Loop
    
    Set oMail = oApp.CreateItem(olMailItem)
    
    With oMail
        .To = oRecipients
        .Display               
    End With
End If
    
ExitPoint:
rs.Close
Set rs = Nothing
Set oMail = Nothing
Set oApp = Nothing
On Error GoTo 0
Exit Function

ErrorHandel:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SendMassMail of Module mdlSendingEmail"
    Resume ExitPoint
      
End Function

Just match the redparts with your table/query name and fieldname that holds email adresses.

Paste the function in a Standard module so you can call it from your project.

JR

 

[djshrew]

Thanks JR, i will give this a try.

--- Edit ---

Added a pic of the error message it get, sure i am doing something stupidly wrong as i am no access expert.

I get error2.jpg first, then error.jpg

 

[djshrew]

think i found a solution, but i have no idea how to implement it.

http://www.jephens.com/2007/05/13/how-to-send-e-mail-from-ms-access-using-outlook/comment-page-1/

i would like to use the loop function attached to this button (See Attached.)

 

[djshrew]

Ok after spending hours on this i have a solution..

Private Sub Command125_Click()

On Error Resume Next

If MsgBox("This will email all contacts in the database.  Continue?", _
           vbExclamation + vbYesNoCancel) = vbYes Then

Dim strMailList As String, rs As Recordset

Set rs = CurrentDb.OpenRecordset("Contacts", dbOpenDynaset) 'Should this be query or table?

With rs
  .MoveFirst

    Do
      strMailList = strMailList & !EmailName & ";" 'changed email to EmailName
        .MoveNext
    Loop Until .EOF

      If Not IsNull(!EmailName) Then
  strMailList = strMailList & !EmailName & ";"
End If
        .Close
End With

  DoCmd.SendObject acSendReport, "Blank", acFormatSNP, strMailList, , , "Barn Bacon News Letter" _
  , , True

End If

Set rs = Nothing

End Sub

I know its a very sloppy way of doing what i want and attached a report which i dont really want, is they a way i can change this peice of code and still get it to open outlook.

  DoCmd.SendObject acSendReport, "Blank", acFormatSNP, strMailList, , , "Barn Bacon News Letter" _
  , , True

--- Edit ----
Found the answer.

DoCmd.SendObject acSendNoObject , "Blank", acFormatSNP, strMailList, , , "Barn Bacon News Letter" _

Just need to figure out how to get from the To: line to the CC: Line.

--------------- Latest EDIT ---------------

BIG BUG.... If there is a contact with no email address it does nothing. does not just skip that person the whole code halts? why? so close yet so far. DOES NOT GIVE AN ERROR JUST DOES NOTHING.

 

[djshrew]

Attached is the latest copy. which does work as i want it to as long as all the email address fields are filled out. However if u remove one the whole thing stops working.

So if u create or have a contact with no email address the whole mailshot stops working.

 

[djshrew]

OK FIXED all the issues

To fix the problem with there being no email address under a contact i based it on a Query. Query just contained email addresses. in the query i added..

 Like "*@*.com" Or Like "*@*.co.uk" Or Like "*@*.net" Or Like "*@8.biz" Or Like "*@*.us" Or Like "*@*.org"

Cannot imagin he will have anyone with another domain, so now it does not matter if the contact does or does not have an email address, as the query is only interested in the email addresses and does not allow blanks. I have tested it and it now works perfect, thanks to all those that tryed to help.

I know its still not the right way to do it, but it is a working solution. it was the only way i manged to muddle my way through.

---- Edit -----

I added the info incase it helped someone else.

---- End Edit ----