Solved VBA command syntax to send email to recipients referring table field (email) (1 Viewer)

M

Manos39

Registered User.
Local time
Today, 15:00
Joined
Feb 14, 2011
Messages
248
Hello,

having trouble specifiying email recipients in vba command syntax:



I have a form with command to send email (click open report from my form and send attached report via outlook),

it is working fine to recipiets whose emails are speciafied in vba

For practical use,

i need to send that report from my table [ypaliloitbl] (where recipients are stored) using the field , having in same table field [statusID]=1
 
Last edited:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:00
Joined
May 7, 2009
Messages
19,245
you need to open a Recordset for table ypaliloitbl:


Code: 
...
...
With Currentdb.OpenRecordset("select [emailField] from ypaliloitbl where [statusid]=1")
   If Not (.BOF And .EOF) Then
       .MoveFirst
   End If
   Do Until .EOF
      stEmail = stEmail & ![emailField] & ";"
      .MoveNext
   Loop
End With
...
...
 
M

Manos39

Registered User.
Local time
Today, 15:00
Joined
Feb 14, 2011
Messages
248
arnelgp said:
you need to open a Recordset for table ypaliloitbl:


Code: 
...
...
With Currentdb.OpenRecordset("select [emailField] from ypaliloitbl where [statusid]=1")
   If Not (.BOF And .EOF) Then
       .MoveFirst
   End If
   Do Until .EOF
      stEmail = stEmail & ![emailField] & ";"
      .MoveNext
   Loop
End With
...
...
Click to expand...
arnelgp hi, even with your answer helping me a lot, i still need help (how to achieve that)
Code: 
Private Sub cmdDiataxiE_mail_Click()

On Error GoTo Err_cmdDiataxiE_mail_Click
    Dim stReport As String
    Dim stWhere As String
    Dim stEmail As String
    Dim stSubject As String
    Dim stEmailMessage As String
    Dim ReportCap As String
    
    stEmailMessage = "DIATAXI, " & vbCrLf & vbCrLf & " " & vbCrLf & "T.E.F.K.K. " & " " & txtImera   'E-mail body
    stSubject = "DIATAXI: " & Me.txtImera 'Email Subject
    stReport = "rptdiataxi" 'Original name of the Report
    ReportCap = "DIATAXI" & Me.txtImera    'Current Report Caption
    stEmail = "manos39@gmail.com;alexarch@otenet.gr"     'E-mail adresses or (Me.E-mail fieldname) ????????????
    DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, ""   'Opens the report
    Reports!rptdiataxi.Caption = "DIATAXI:" & txtImera    'Renames the Report
    DoCmd.SendObject acSendReport, stReport, "PDFFormat(*.pdf)", stEmail, , , stSubject, stEmailMessage, True, "" 'Send out the E-mail
    DoCmd.Close acReport, stReport, acSaveNo
      
    
Exit_cmdDiataxiE_mail_Click:
    Exit Sub
Err_cmdDiataxiE_mail_Click:
    MsgBox Err.Description
    Resume Exit_cmdDiataxiE_mail_Click
    
End Sub
with syntax in my code which is
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:00
Joined
May 7, 2009
Messages
19,245
Code: 
Private Sub cmdDiataxiE_mail_Click()
On Error GoTo Err_cmdDiataxiE_mail_Click
    Dim stReport As String
    Dim stWhere As String
    Dim stEmail As String
    Dim stSubject As String
    Dim stEmailMessage As String
    Dim ReportCap As String
    
    stEmailMessage = "DIATAXI, " & vbCrLf & vbCrLf & " " & vbCrLf & "T.E.F.K.K. " & " " & txtImera   'E-mail body
    stSubject = "DIATAXI: " & Me.txtImera 'Email Subject
    stReport = "rptdiataxi" 'Original name of the Report
    ReportCap = "DIATAXI" & Me.txtImera    'Current Report Caption
    
    'stEmail = "manos39@gmail.com;alexarch@otenet.gr"     'E-mail adresses or (Me.E-mail fieldname) ????????????
    
    ':)
    ' note you change "EmailField" (without quote) with the correct fieldname
    ' also change the ypaliloitbl to the correct table name.
    ';)
    
    With CurrentDb.OpenRecordset("select [EmailField] from ypaliloitbl where [statusid]=1")
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            stEmail = stEmail & ![EmailField] & ";"
            .MoveNext
        Loop
    End With
    
    DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, ""   'Opens the report
    Reports!rptdiataxi.Caption = "DIATAXI:" & txtImera    'Renames the Report
    DoCmd.SendObject acSendReport, stReport, "PDFFormat(*.pdf)", stEmail, , , stSubject, stEmailMessage, True, "" 'Send out the E-mail
    DoCmd.Close acReport, stReport, acSaveNo
      
    
Exit_cmdDiataxiE_mail_Click:
    Exit Sub
Err_cmdDiataxiE_mail_Click:
    MsgBox Err.Description
    Resume Exit_cmdDiataxiE_mail_Click
    
End Sub
 
M

Manos39

Registered User.
Local time
Today, 15:00
Joined
Feb 14, 2011
Messages
248
email was not send arnelgp, is it because my email field name is "email" ?
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:00
Joined
May 7, 2009
Messages
19,245
Manos39 said:
email was not send arnelgp, is it because my email field name is "email" ?
Click to expand...
that is why i put instruction on the code i gave you, since I don't know
your e-mail field name?
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 23:00
Joined
Sep 21, 2011
Messages
14,310
arnelgp said:
that is why i put instruction on the code i gave you, since I don't know
your e-mail field name?
Click to expand...
In case you still do not know what that was

':)
' note you change "EmailField" (without quote) with the correct fieldname
' also change the ypaliloitbl to the correct table name.
';)
Click to expand...
 
M

Manos39

Registered User.
Local time
Today, 15:00
Joined
Feb 14, 2011
Messages
248
arnelgp said:
that is why i put instruction on the code i gave you, since I don't know
your e-mail field name?
Click to expand...
i saw that excuse me still changed with those intraction sending was not achieved with message:
"uknown message recipient(s); message was not sent"

Code: 
Private Sub cmdDiataxiE_mail_Click()
On Error GoTo Err_cmdDiataxiE_mail_Click
    Dim stReport As String
    Dim stWhere As String
    Dim stEmail As String
    Dim stSubject As String
    Dim stEmailMessage As String
    Dim ReportCap As String
   
    stEmailMessage = "ÄÉÁÔÁÎÇ ÕÐÇÑÅÓÉÁÓ, " & vbCrLf & vbCrLf & " " & vbCrLf & "T.E.Ö.K.K. ÊÏÌÏÔÇÍÇÓ " & " " & txtImera   'E-mail body
    stSubject = "ÄÉÁÔÁÎÇ ÕÐÇÑÅÓÉÁÓ: " & Me.txtImera 'Email Subject
    stReport = "rptdiataxi" 'Original name of the Report
    ReportCap = "ÄÉÁÔÁÎÇ ÕÐÇÑÅÓÉÁÓ" & Me.txtImera    'Current Report Caption
   
   With CurrentDb.OpenRecordset("select [email] from ypaliloitbl where [statusid]=1")
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            stEmail = stEmail & ![email] & ";"
            .MoveNext
        Loop
    End With
   
    DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, ""   'Opens the report
    Reports!rptdiataxi.Caption = "DIATAXI:" & txtImera    'Renames the Report
    DoCmd.SendObject acSendReport, stReport, "PDFFormat(*.pdf)", stEmail, , , stSubject, stEmailMessage, True, "" 'Send out the E-mail
    DoCmd.Close acReport, stReport, acSaveNo
     
   
Exit_cmdDiataxiE_mail_Click:
    Exit Sub
Err_cmdDiataxiE_mail_Click:
    MsgBox Err.Description
    Resume Exit_cmdDiataxiE_mail_Click
   
End Sub

also not all recipients have email is it a matter of whether nz is needed?
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 23:00
Joined
Sep 21, 2011
Messages
14,310
Well it would make sense to me to NOT use email fields where there is no value in them? :(
So exclude them in the sql perhaps?
 
M

Manos39

Registered User.
Local time
Today, 15:00
Joined
Feb 14, 2011
Messages
248
Still cant get it work..
did some tests and in line

Code: 
 stEmail = stEmail & ![email] & ";"
if subtructed the & ";"
recipients list is generated but canot be sent,
else put above code ,returns
"uknown message recipient(s); message was not sent"
 
Last edited:
M

Manos39

Registered User.
Local time
Today, 15:00
Joined
Feb 14, 2011
Messages
248
Problem solved after tests

from arnelgp's code and in line
stEmail = stEmail & ! & ";"



to
stEmail = stEmail + ";"



arnelgp thank you!!
 
Last edited:
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 23:00
Joined
Sep 21, 2011
Messages
14,310
No arnel's code was correct. You just exclude email fields with nothing in them As I mentioned you cannot send an email unless you have an address?
Also unless you want Null replication, you use & not + :(
 
M

Manos39

Registered User.
Local time
Today, 15:00
Joined
Feb 14, 2011
Messages
248
Gasman i would love this to work without wrong code and i wouldnt want null replication to be there, but i wasnt able to send emails via outlook
"uknown message recipient(s); message was not sent" with

Code: 
stEmail = stEmail & ![email] & ";"

what would be wrong?

tryied that with just stEmail = stEmail & !
and outlook had recipients stucked (wouldnt work)
tried again with [B]stEmail = stEmail & ![email] & ";"[/B] same message " uknown message recipient(s); message was not sent"

what possibly is wrong?
I thought i solved it replacing "&" with "+"
 
M

Manos39

Registered User.
Local time
Today, 15:00
Joined
Feb 14, 2011
Messages
248
Solved with arnlel's code and Gasman's help about null values that should be blocking whole thing (producing correct values so outlook would work) so i returned with an sql query besed on my table where condition of isnull = false refrase that to vba and boom

Code: 
Private Sub cmdDiataxiE_mail_Click()
On Error GoTo Err_cmdDiataxiE_mail_Click
    Dim stReport As String
    Dim stWhere As String
    Dim stSubject As String
    Dim stEmailMessage As String
    Dim ReportCap As String
    Dim stEmail As String
   
    stEmailMessage = "DIATAXI:, " & vbCrLf & vbCrLf & " " & vbCrLf & "T.E.K.K.  " & " " & txtImera   'E-mail body
    stSubject = "DIATAXI:: " & Me.txtImera 'Email Subject
    stReport = "rptdiataxi" 'Original name of the Report
    ReportCap = "DIATAXI:" & Me.txtImera    'Current Report Caption
    stEmail = stEmail
   
   With CurrentDb.OpenRecordset(" SELECT ypaliloitbl.email, ypaliloitbl.statusID, ypaliloitbl.reportstatusID " & _
" FROM ypaliloitbl " & _
" WHERE (((IsNull([email]))=False)) " & _
" GROUP BY ypaliloitbl.email, ypaliloitbl.statusID, ypaliloitbl.reportstatusID " & _
" HAVING (((ypaliloitbl.statusID)=1) AND ((ypaliloitbl.reportstatusID)=1));")
 
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            stEmail = stEmail & ![email] & ";"
          .MoveNext
        Loop
    End With
 
   
    DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, ""   'Opens the report
    Reports!rptdiataxi.Caption = "DIATAXI:" & txtImera    'Renames the Report
    DoCmd.SendObject acSendReport, stReport, "PDFFormat(*.pdf)", stEmail, , , stSubject, stEmailMessage, True, "" 'Send out the E-mail
    DoCmd.Close acReport, stReport, acSaveNo
     
   
Exit_cmdDiataxiE_mail_Click:
    Exit Sub
Err_cmdDiataxiE_mail_Click:
    MsgBox Err.Description
    Resume Exit_cmdDiataxiE_mail_Click
   
End Sub
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 23:00
Joined
Sep 21, 2011
Messages
14,310
You should debug.print your variables to see what you actually have, not what you think you have.
 
You must log in or register to reply here.

Similar threads

1
send an email using recipients from a query
Replies
2
Views
418
Uncle Gizmo
Uncle Gizmo
M
VBA output report to PDF and email
Replies
18
Views
778
Pat Hartman
P
L
Send looping Email
Replies
3
Views
325
looma123
L
C
Inserting multiple rows into an email created by VBA
Replies
6
Views
215
ebs17
E
A
Unable to send Salary Slip via Email
Replies
4
Views
164
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom