multiple email with individual ref number

[ypma]

In a previous post I received assistance in creating a module to send email message to a rs of members. This work well , but i would like to place an individual reference number for each email to enable me to ask the member to refer to it when responding. I assume this would require an extra loop the same as the email addressees . If so please advise on how this could be done . I will attach a test db . Access 2010 . If the code for the send email is required i am happy to supply it .

Regards Ypma

 

[pbaldy]

Add the ID number to the recordset SQL and then you can pull that field into the body.

 

[ypma]

pbaldy: Thank you for looking at my Post. i fully understand that i must some how pull my FFIDNO into the body of the SQL. The how is what is stumping me and would appreciate a little more guidance . the following is part of the script .
# Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryemailcontact")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs!EmailAddress) Then
vRecipientList = vRecipientList & rs!EmailAddress & ";"
rs.MoveNext
Else
rs.MoveNext
End If
Loop Until rs.EOF

vMsg = FFIDNO & "Your Reference is" & " " & " " & Me.txtMessage.Value
vSubject = Me.txtSubject.Value '"Your Subject here 255 Characters ..."#
Notice i have placed the ID prior to the txtmessage.value but the Same id appears in all emails.

Regards Ypma

 

[theDBguy]

Hi. Does qryemailcontact include the FFIDNO column? If not, that's what Paul was saying. You need to add it to the query.

 

[Gasman]

If each email needs to contain a unique id then I would have thought you need to send one email to each recipient, not as you were doing one email to everyone?

So you need to construct the email for each recipient?

You would create the static part of the email once outside the loop and then repeat for as many recipients in the recordset, using rs! FFIDNO

HTH

Edit: I would also weed out NULL email addresses in the query, rather than have to test in the code?

 

[ypma]

theDbguy: Yes , the id is included in the query!! its the email addressees that are being picked up in the loop. I know some of you professional are not happy about opening up database , but i cannot show you my problem any other way.
Regards Ypma

 

[ypma]

GasMan: Thank you for your input. The test example only shows three records
, however there are nearly 200 team members. I was trying to give the user the ability to ask members to quote their id if replying . I have in the past used word merge and then send email from the word document . So there is no easy solution to my question , but thank you for taking time out to put me right.

Regards Ypma

 

[Gasman]

I only have 2007, so unable to open your DB.
You would need to post all the code that is used to send the email, not just a snippet?

Within code tags as well please, if you do.

 

[ypma]

Gasman: sorry about the snippet. here is the full code from the send email command button.
Private Sub SendeMail()

Dim rs As Recordset
Dim vRecipientList As String
Dim vMsg As String
Dim vSubject As String
Dim vID As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryemailcontact")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs!EmailAddress) Then
vRecipientList = vRecipientList & rs!EmailAddress + rs!FFIDNO & ";"
rs.MoveNext
Else
rs.MoveNext
End If

Loop Until rs.EOF

vMsg = "Your Reference is" & " " & " " & Me.txtMessage.Value
vSubject = Me.txtSubject.Value '"Your Subject here 255 Characters ..."


#DoCmd.SetWarnings False

DoCmd.SendObject , , acFormatRTF, "rfortune@ntlworld.com", , vRecipientList, vSubject, vMsg, False, True

MsgBox ("Email successfully eMailed!")
DoCmd.SetWarnings True
Else
MsgBox "No contacts."
End If

End Sub#

The fact that you are wiling to take another look, is very much appreciate,
as mentioned before i am not a professional and rely on this forum for the best possible practice .

Regards Ypma

 

[theDBguy]

Hi. You could try.

          vMsg = "Your Reference is " & [COLOR=Red][B]rs!FFIDNO[/B][/COLOR] & " " & Me.txtMessage.Value

 

[Gasman]

You are still concatenating all the email addresses?
I believe you just need one at a time and have the DoCmd inside the loop.?

You cannot just concatenate the FFIDNO to the email address, that will make it invalid?

Plus you did not use code tags as requested, so we have lost any indentation?

theDBguy has shown one option where to place FFIDNO

 

[Gasman]

I was thinking along these lines.

Private Sub SendeMail()

    Dim rs As Recordset
    Dim vRecipientList As String
    Dim vMsg As String
    Dim vSubject As String
    Dim vID As String

    DoCmd.SetWarnings False

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryemailcontact")
    If rs.RecordCount > 0 Then
        vSubject = Me.txtSubject.Value    '"Your Subject here 255 Characters ..."
        rs.MoveFirst
        Do
            If Not IsNull(rs!EmailAddress) Then
                vMsg = "Your Reference is " & rs!FFIDNO & " " & Me.txtMessage.Value
                DoCmd.SendObject , , acFormatRTF, , , rs!EmailAddress, vSubject, vMsg, False, True
            End If
            rs.MoveNext
        Loop Until rs.EOF

        MsgBox ("Emails successfully sent!")
    Else
        MsgBox "No contacts."
    End If
    DoCmd.SetWarnings True

End Sub

Sent as BCC

HTH

 

[ypma]

theDbguy: Thanks for your suggestion i am hopeful that the rs!FFIDNO is inserted somewhere into the script. On entering your suggestion I receive an error message
"Run time error" 3021 No current record.

Clearly it does not like it have you any further idea's ?

Regards Ypma

 

[theDBguy]

theDbguy: Thanks for your suggestion i am hopeful that the rs!FFIDNO is inserted somewhere into the script. On entering your suggestion I receive an error message
"Run time error" 3021 No current record.

Clearly it does not like it have you any further idea's ?

Regards Ypma

Does the query have any records? You might go ahead and try Gasman's suggestion, since it's more complete and let us know how it goes.

 

[Gasman]

theDbguy: Thanks for your suggestion i am hopeful that the rs!FFIDNO is inserted somewhere into the script. On entering your suggestion I receive an error message
"Run time error" 3021 No current record.

Clearly it does not like it have you any further idea's ?

Regards Ypma

That will be likely due to the fact that you have not moved that line, and that gets runs after EOF of the recordset in your code.

 

[ypma]

Gasman: Thank you for the code , it probably needs tweaking as it opened up outlook,but did not send the emails.i have saved my demo as a ms2003 and hope you will be able to open it and solve my problem.

Hopefully Ypma

 

[theDBguy]

Hi ypma. When still testing your Outlook code, there's no need to send the email out yet. Simply displaying it should be enough. Once you got the email working correctly, then you can change the code to auto-send them. Just my 2 cents...

 

[Gasman]

Try putting the static email address back which was the To address.
I did not think you would want 200 emails sent to that address?

Walk through the code with F8 in debug mode.

 

[Gasman]

Works for me, except I had to change recordset to DAO as I got type mismatch on set rs and added a To address.
Plus I got warnings from Outlook with each email that a program was trying to send an email.

HTH

 

[Gasman]

Seems the DoCmd.SendObject insists on an email address in the To field.?
I tried without, just using a space and got unknown recipient.

If you leave it out ltogether, then it opens outlook with the cursor in the To field

HTH