Enter Date on Emailing

B

Bob Vance

I have a code which on deactivate of report if Owner has an email address
the report is emailed, can I add to the code:
Enter today's date to table/field tblOwnerInfo.Emaildate to the same
"OwnerID"
Thanks for any help....Bob

Private Sub Report_Deactivate()


On Error GoTo Error_Handler

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, dtInvDate As Date, varInvNum As Variant, _
idHorse As Long, strHorse As String


Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp
As Integer

If CurrentProject.AllForms("frmModify").IsLoaded = True Then
lngID = DLookup("OwnerID", "tblInvoice", "InvoiceID = " _
& Form_frmModify.lstModify.Column(0))
ElseIf CurrentProject.AllForms("frmModifyInvoiceClient").IsLoaded = True
Then
lngID = DLookup("OwnerID", "tblInvoice", "InvoiceID = " _
& Form_frmModifyInvoiceClient.lstModify.value)
Else
Exit Sub
End If


strMail = Nz(DLookup("Email", "tblOwnerInfo", "OwnerID = " & lngID), "")


If Not IsEmailOn Or Not IsOwnerWithEmail(lngID) Then
Exit Sub
End If


dtInvDate = Me.tbInvoiceDate
varInvNum = Me.tbInvoiceNumber
idHorse = Nz(Me.tbHorseID, 0)
If idHorse <> 0 Then
strHorse = Nz(DLookup("[Name]", "qryHorseNameAll", "[HorseID]=" &
idHorse), "")
Else
strHorse = ""
End If

strBodyMsg = "Dear "
strBodyMsg = strBodyMsg & Nz(DLookup("[ClientTitle]", "tblOwnerInfo",
"[OwnerID]=" & lngID), " ") & " "
strBodyMsg = strBodyMsg & Nz(DLookup("[OwnerLastName]", "tblOwnerInfo",
"[OwnerID]=" & lngID), " Owner")
strBodyMsg = strBodyMsg & "," & Chr(10) & Chr(10) & Chr(13) _
& "Attached is your " & varInvNum & " Dated " & Format(dtInvDate,
"d-mmm-yyyy") _
& IIf(Len(strHorse) > 0, " for " & strHorse, "") & "." _
& eMailSignature("Best Regards", True) & Chr(10) & Chr(10) & Chr(13) _
& DownloadMessage("snp")




If strMail = "Null" Or Len(strMail) = 0 Or _
DLookup("[MailFlag]", "tblAdminSetup") = False Then
Exit Sub
End If


msgTitle = "E-Mail"
msgBtns = vbYes + vbQuestion + vbDefaultButton2 + vbApplicationModal
msgPmt = " Create E-mail "
msgResp = MsgBox(msgPmt, msgBtns, msgTitle)
If msgResp = vbCancel Then
Exit Sub
Else
blEditMail = IIf(msgResp = vbYes, False, True)
End If

DoCmd.SendObject acSendReport, Me.Name, acFormatSNP, strMail, , , "Your
Invoice " & IIf(Len(strHorse) > 0, " / " & strHorse, ""), _
strBodyMsg, blEditMail



Exit Sub

If MsgBox("Do you want to send Email??", vbYesNo + vbDefaultButton2)
= vbYes Then

DoCmd.SendObject acSendReport, Me.Name, acFormatRTF,
strMail, , , _
"Your Invoice", strBodyMsg, True
End If


Exit Sub

Error_Handler:
Select Case Err.Number
Case 2501
Exit Sub
Case 2487
Resume Next
Case Else
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description, , "Untrapped Error"

End Select

End Sub
 
D

Douglas J. Steele

CurrentDb.Execute "UPDATE tblOwnerInfo " & _
"SET Emaildate = Now() " & _
"WHERE OwnerID = " & lngId, dbFailOnError

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bob Vance said:
I have a code which on deactivate of report if Owner has an email address
the report is emailed, can I add to the code:
Enter today's date to table/field tblOwnerInfo.Emaildate to the same
"OwnerID"
Thanks for any help....Bob

Private Sub Report_Deactivate()


On Error GoTo Error_Handler

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, dtInvDate As Date, varInvNum As Variant, _
idHorse As Long, strHorse As String


Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp
As Integer

If CurrentProject.AllForms("frmModify").IsLoaded = True Then
lngID = DLookup("OwnerID", "tblInvoice", "InvoiceID = " _
& Form_frmModify.lstModify.Column(0))
ElseIf CurrentProject.AllForms("frmModifyInvoiceClient").IsLoaded =
True Then
lngID = DLookup("OwnerID", "tblInvoice", "InvoiceID = " _
& Form_frmModifyInvoiceClient.lstModify.value)
Else
Exit Sub
End If


strMail = Nz(DLookup("Email", "tblOwnerInfo", "OwnerID = " & lngID),
"")


If Not IsEmailOn Or Not IsOwnerWithEmail(lngID) Then
Exit Sub
End If


dtInvDate = Me.tbInvoiceDate
varInvNum = Me.tbInvoiceNumber
idHorse = Nz(Me.tbHorseID, 0)
If idHorse <> 0 Then
strHorse = Nz(DLookup("[Name]", "qryHorseNameAll", "[HorseID]=" &
idHorse), "")
Else
strHorse = ""
End If

strBodyMsg = "Dear "
strBodyMsg = strBodyMsg & Nz(DLookup("[ClientTitle]", "tblOwnerInfo",
"[OwnerID]=" & lngID), " ") & " "
strBodyMsg = strBodyMsg & Nz(DLookup("[OwnerLastName]", "tblOwnerInfo",
"[OwnerID]=" & lngID), " Owner")
strBodyMsg = strBodyMsg & "," & Chr(10) & Chr(10) & Chr(13) _
& "Attached is your " & varInvNum & " Dated " & Format(dtInvDate,
"d-mmm-yyyy") _
& IIf(Len(strHorse) > 0, " for " & strHorse, "") & "." _
& eMailSignature("Best Regards", True) & Chr(10) & Chr(10) & Chr(13) _
& DownloadMessage("snp")




If strMail = "Null" Or Len(strMail) = 0 Or _
DLookup("[MailFlag]", "tblAdminSetup") = False Then
Exit Sub
End If


msgTitle = "E-Mail"
msgBtns = vbYes + vbQuestion + vbDefaultButton2 + vbApplicationModal
msgPmt = " Create E-mail "
msgResp = MsgBox(msgPmt, msgBtns, msgTitle)
If msgResp = vbCancel Then
Exit Sub
Else
blEditMail = IIf(msgResp = vbYes, False, True)
End If

DoCmd.SendObject acSendReport, Me.Name, acFormatSNP, strMail, , , "Your
Invoice " & IIf(Len(strHorse) > 0, " / " & strHorse, ""), _
strBodyMsg, blEditMail



Exit Sub

If MsgBox("Do you want to send Email??", vbYesNo +
vbDefaultButton2) = vbYes Then

DoCmd.SendObject acSendReport, Me.Name, acFormatRTF,
strMail, , , _
"Your Invoice", strBodyMsg, True
End If


Exit Sub

Error_Handler:
Select Case Err.Number
Case 2501
Exit Sub
Case 2487
Resume Next
Case Else
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description, , "Untrapped Error"

End Select

End Sub
Click to expand...
 
B

Bob Vance

Thanks Douglas for your time, Brilliant just one thing usally when i close
my report by using the Esc key if I do this now I am getting the error "
Error Number 3509
Description:Operation canceled by user
But if I right click close everthing works fine
Regards Bob

Douglas J. Steele said:
CurrentDb.Execute "UPDATE tblOwnerInfo " & _
"SET Emaildate = Now() " & _
"WHERE OwnerID = " & lngId, dbFailOnError

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bob Vance said:
I have a code which on deactivate of report if Owner has an email address
the report is emailed, can I add to the code:
Enter today's date to table/field tblOwnerInfo.Emaildate to the same
"OwnerID"
Thanks for any help....Bob

Private Sub Report_Deactivate()


On Error GoTo Error_Handler

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, dtInvDate As Date, varInvNum As Variant, _
idHorse As Long, strHorse As String


Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp
As Integer

If CurrentProject.AllForms("frmModify").IsLoaded = True Then
lngID = DLookup("OwnerID", "tblInvoice", "InvoiceID = " _
& Form_frmModify.lstModify.Column(0))
ElseIf CurrentProject.AllForms("frmModifyInvoiceClient").IsLoaded =
True Then
lngID = DLookup("OwnerID", "tblInvoice", "InvoiceID = " _
& Form_frmModifyInvoiceClient.lstModify.value)
Else
Exit Sub
End If


strMail = Nz(DLookup("Email", "tblOwnerInfo", "OwnerID = " & lngID),
"")


If Not IsEmailOn Or Not IsOwnerWithEmail(lngID) Then
Exit Sub
End If


dtInvDate = Me.tbInvoiceDate
varInvNum = Me.tbInvoiceNumber
idHorse = Nz(Me.tbHorseID, 0)
If idHorse <> 0 Then
strHorse = Nz(DLookup("[Name]", "qryHorseNameAll", "[HorseID]=" &
idHorse), "")
Else
strHorse = ""
End If

strBodyMsg = "Dear "
strBodyMsg = strBodyMsg & Nz(DLookup("[ClientTitle]", "tblOwnerInfo",
"[OwnerID]=" & lngID), " ") & " "
strBodyMsg = strBodyMsg & Nz(DLookup("[OwnerLastName]",
"tblOwnerInfo", "[OwnerID]=" & lngID), " Owner")
strBodyMsg = strBodyMsg & "," & Chr(10) & Chr(10) & Chr(13) _
& "Attached is your " & varInvNum & " Dated " & Format(dtInvDate,
"d-mmm-yyyy") _
& IIf(Len(strHorse) > 0, " for " & strHorse, "") & "." _
& eMailSignature("Best Regards", True) & Chr(10) & Chr(10) & Chr(13) _
& DownloadMessage("snp")




If strMail = "Null" Or Len(strMail) = 0 Or _
DLookup("[MailFlag]", "tblAdminSetup") = False Then
Exit Sub
End If


msgTitle = "E-Mail"
msgBtns = vbYes + vbQuestion + vbDefaultButton2 + vbApplicationModal
msgPmt = " Create E-mail "
msgResp = MsgBox(msgPmt, msgBtns, msgTitle)
If msgResp = vbCancel Then
Exit Sub
Else
blEditMail = IIf(msgResp = vbYes, False, True)
End If

DoCmd.SendObject acSendReport, Me.Name, acFormatSNP, strMail, , ,
"Your Invoice " & IIf(Len(strHorse) > 0, " / " & strHorse, ""), _
strBodyMsg, blEditMail



Exit Sub

If MsgBox("Do you want to send Email??", vbYesNo +
vbDefaultButton2) = vbYes Then

DoCmd.SendObject acSendReport, Me.Name, acFormatRTF,
strMail, , , _
"Your Invoice", strBodyMsg, True
End If


Exit Sub

Error_Handler:
Select Case Err.Number
Case 2501
Exit Sub
Case 2487
Resume Next
Case Else
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description, , "Untrapped Error"

End Select

End Sub
Click to expand...
Click to expand...
 
B

Bob Vance

Douglas, can I get around it by adding my de-activate code to my control
button that is for opening report for emailing...Thanks for your help Bob

Private Sub cmdEmail_Click()
If lstModify.value = "" Or IsNull(lstModify.value) Then
MsgBox "Please Select Invoice.", vbApplicationModal + vbOKOnly +
vbInformation
Exit Sub
End If


DoCmd.OpenReport "rptInvoiceModify", acViewPreview

End Sub

Bob Vance said:
Thanks Douglas for your time, Brilliant just one thing usally when i close
my report by using the Esc key if I do this now I am getting the error "
Error Number 3509
Description:Operation canceled by user
But if I right click close everthing works fine
Regards Bob

Douglas J. Steele said:
CurrentDb.Execute "UPDATE tblOwnerInfo " & _
"SET Emaildate = Now() " & _
"WHERE OwnerID = " & lngId, dbFailOnError

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bob Vance said:
I have a code which on deactivate of report if Owner has an email address
the report is emailed, can I add to the code:
Enter today's date to table/field tblOwnerInfo.Emaildate to the same
"OwnerID"
Thanks for any help....Bob

Private Sub Report_Deactivate()


On Error GoTo Error_Handler

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, dtInvDate As Date, varInvNum As Variant, _
idHorse As Long, strHorse As String


Dim msgPmt As String, msgBtns As Integer, msgTitle As String,
msgResp As Integer

If CurrentProject.AllForms("frmModify").IsLoaded = True Then
lngID = DLookup("OwnerID", "tblInvoice", "InvoiceID = " _
& Form_frmModify.lstModify.Column(0))
ElseIf CurrentProject.AllForms("frmModifyInvoiceClient").IsLoaded =
True Then
lngID = DLookup("OwnerID", "tblInvoice", "InvoiceID = " _
& Form_frmModifyInvoiceClient.lstModify.value)
Else
Exit Sub
End If


strMail = Nz(DLookup("Email", "tblOwnerInfo", "OwnerID = " & lngID),
"")


If Not IsEmailOn Or Not IsOwnerWithEmail(lngID) Then
Exit Sub
End If


dtInvDate = Me.tbInvoiceDate
varInvNum = Me.tbInvoiceNumber
idHorse = Nz(Me.tbHorseID, 0)
If idHorse <> 0 Then
strHorse = Nz(DLookup("[Name]", "qryHorseNameAll", "[HorseID]=" &
idHorse), "")
Else
strHorse = ""
End If

strBodyMsg = "Dear "
strBodyMsg = strBodyMsg & Nz(DLookup("[ClientTitle]", "tblOwnerInfo",
"[OwnerID]=" & lngID), " ") & " "
strBodyMsg = strBodyMsg & Nz(DLookup("[OwnerLastName]",
"tblOwnerInfo", "[OwnerID]=" & lngID), " Owner")
strBodyMsg = strBodyMsg & "," & Chr(10) & Chr(10) & Chr(13) _
& "Attached is your " & varInvNum & " Dated " & Format(dtInvDate,
"d-mmm-yyyy") _
& IIf(Len(strHorse) > 0, " for " & strHorse, "") & "." _
& eMailSignature("Best Regards", True) & Chr(10) & Chr(10) & Chr(13)
_
& DownloadMessage("snp")




If strMail = "Null" Or Len(strMail) = 0 Or _
DLookup("[MailFlag]", "tblAdminSetup") = False Then
Exit Sub
End If


msgTitle = "E-Mail"
msgBtns = vbYes + vbQuestion + vbDefaultButton2 + vbApplicationModal
msgPmt = " Create E-mail "
msgResp = MsgBox(msgPmt, msgBtns, msgTitle)
If msgResp = vbCancel Then
Exit Sub
Else
blEditMail = IIf(msgResp = vbYes, False, True)
End If

DoCmd.SendObject acSendReport, Me.Name, acFormatSNP, strMail, , ,
"Your Invoice " & IIf(Len(strHorse) > 0, " / " & strHorse, ""), _
strBodyMsg, blEditMail



Exit Sub

If MsgBox("Do you want to send Email??", vbYesNo +
vbDefaultButton2) = vbYes Then

DoCmd.SendObject acSendReport, Me.Name, acFormatRTF,
strMail, , , _
"Your Invoice", strBodyMsg, True
End If


Exit Sub

Error_Handler:
Select Case Err.Number
Case 2501
Exit Sub
Case 2487
Resume Next
Case Else
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description, , "Untrapped Error"

End Select

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Ã

ãÌãæÚÉ ÇáãÓÊÔÇÑ ÇÈææÍíÏ

þþßÊÈ "Bob Vance said:
I have a code which on deactivate of report if Owner has an email address
the report is emailed, can I add to the code:
Enter today's date to table/field tblOwnerInfo.Emaildate to the same
"OwnerID"
Thanks for any help....Bob

Private Sub Report_Deactivate()


On Error GoTo Error_Handler

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, dtInvDate As Date, varInvNum As Variant, _
idHorse As Long, strHorse As String


Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp
As Integer

If CurrentProject.AllForms("frmModify").IsLoaded = True Then
lngID = DLookup("OwnerID", "tblInvoice", "InvoiceID = " _
& Form_frmModify.lstModify.Column(0))
ElseIf CurrentProject.AllForms("frmModifyInvoiceClient").IsLoaded =
True Then
lngID = DLookup("OwnerID", "tblInvoice", "InvoiceID = " _
& Form_frmModifyInvoiceClient.lstModify.value)
Else
Exit Sub
End If


strMail = Nz(DLookup("Email", "tblOwnerInfo", "OwnerID = " & lngID),
"")


If Not IsEmailOn Or Not IsOwnerWithEmail(lngID) Then
Exit Sub
End If


dtInvDate = Me.tbInvoiceDate
varInvNum = Me.tbInvoiceNumber
idHorse = Nz(Me.tbHorseID, 0)
If idHorse <> 0 Then
strHorse = Nz(DLookup("[Name]", "qryHorseNameAll", "[HorseID]=" &
idHorse), "")
Else
strHorse = ""
End If

strBodyMsg = "Dear "
strBodyMsg = strBodyMsg & Nz(DLookup("[ClientTitle]", "tblOwnerInfo",
"[OwnerID]=" & lngID), " ") & " "
strBodyMsg = strBodyMsg & Nz(DLookup("[OwnerLastName]", "tblOwnerInfo",
"[OwnerID]=" & lngID), " Owner")
strBodyMsg = strBodyMsg & "," & Chr(10) & Chr(10) & Chr(13) _
& "Attached is your " & varInvNum & " Dated " & Format(dtInvDate,
"d-mmm-yyyy") _
& IIf(Len(strHorse) > 0, " for " & strHorse, "") & "." _
& eMailSignature("Best Regards", True) & Chr(10) & Chr(10) & Chr(13) _
& DownloadMessage("snp")




If strMail = "Null" Or Len(strMail) = 0 Or _
DLookup("[MailFlag]", "tblAdminSetup") = False Then
Exit Sub
End If


msgTitle = "E-Mail"
msgBtns = vbYes + vbQuestion + vbDefaultButton2 + vbApplicationModal
msgPmt = " Create E-mail "
msgResp = MsgBox(msgPmt, msgBtns, msgTitle)
If msgResp = vbCancel Then
Exit Sub
Else
blEditMail = IIf(msgResp = vbYes, False, True)
End If

DoCmd.SendObject acSendReport, Me.Name, acFormatSNP, strMail, , , "Your
Invoice " & IIf(Len(strHorse) > 0, " / " & strHorse, ""), _
strBodyMsg, blEditMail



Exit Sub

If MsgBox("Do you want to send Email??", vbYesNo +
vbDefaultButton2) = vbYes Then

DoCmd.SendObject acSendReport, Me.Name, acFormatRTF,
strMail, , , _
"Your Invoice", strBodyMsg, True
End If


Exit Sub

Error_Handler:
Select Case Err.Number
Case 2501
Exit Sub
Case 2487
Resume Next
Case Else
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description, , "Untrapped Error"

End Select

End Sub
Click to expand...
 
D

Douglas J. Steele

You can simplify that to

Private Sub cmdEmail_Click()

If Len(lstModify.value & vbNullString) = 0Then
MsgBox "Please Select Invoice.", _
vbApplicationModal + vbOKOnly + vbInformation
Else
DoCmd.OpenReport "rptInvoiceModify", acViewPreview
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bob Vance said:
Douglas, can I get around it by adding my de-activate code to my control
button that is for opening report for emailing...Thanks for your help Bob

Private Sub cmdEmail_Click()
If lstModify.value = "" Or IsNull(lstModify.value) Then
MsgBox "Please Select Invoice.", vbApplicationModal + vbOKOnly +
vbInformation
Exit Sub
End If


DoCmd.OpenReport "rptInvoiceModify", acViewPreview

End Sub

Bob Vance said:
Thanks Douglas for your time, Brilliant just one thing usally when i
close my report by using the Esc key if I do this now I am getting the
error "
Error Number 3509
Description:Operation canceled by user
But if I right click close everthing works fine
Regards Bob

Douglas J. Steele said:
CurrentDb.Execute "UPDATE tblOwnerInfo " & _
"SET Emaildate = Now() " & _
"WHERE OwnerID = " & lngId, dbFailOnError

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a code which on deactivate of report if Owner has an email
address the report is emailed, can I add to the code:
Enter today's date to table/field tblOwnerInfo.Emaildate to the same
"OwnerID"
Thanks for any help....Bob

Private Sub Report_Deactivate()


On Error GoTo Error_Handler

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, dtInvDate As Date, varInvNum As Variant, _
idHorse As Long, strHorse As String


Dim msgPmt As String, msgBtns As Integer, msgTitle As String,
msgResp As Integer

If CurrentProject.AllForms("frmModify").IsLoaded = True Then
lngID = DLookup("OwnerID", "tblInvoice", "InvoiceID = " _
& Form_frmModify.lstModify.Column(0))
ElseIf CurrentProject.AllForms("frmModifyInvoiceClient").IsLoaded =
True Then
lngID = DLookup("OwnerID", "tblInvoice", "InvoiceID = " _
& Form_frmModifyInvoiceClient.lstModify.value)
Else
Exit Sub
End If


strMail = Nz(DLookup("Email", "tblOwnerInfo", "OwnerID = " & lngID),
"")


If Not IsEmailOn Or Not IsOwnerWithEmail(lngID) Then
Exit Sub
End If


dtInvDate = Me.tbInvoiceDate
varInvNum = Me.tbInvoiceNumber
idHorse = Nz(Me.tbHorseID, 0)
If idHorse <> 0 Then
strHorse = Nz(DLookup("[Name]", "qryHorseNameAll", "[HorseID]="
& idHorse), "")
Else
strHorse = ""
End If

strBodyMsg = "Dear "
strBodyMsg = strBodyMsg & Nz(DLookup("[ClientTitle]",
"tblOwnerInfo", "[OwnerID]=" & lngID), " ") & " "
strBodyMsg = strBodyMsg & Nz(DLookup("[OwnerLastName]",
"tblOwnerInfo", "[OwnerID]=" & lngID), " Owner")
strBodyMsg = strBodyMsg & "," & Chr(10) & Chr(10) & Chr(13) _
& "Attached is your " & varInvNum & " Dated " & Format(dtInvDate,
"d-mmm-yyyy") _
& IIf(Len(strHorse) > 0, " for " & strHorse, "") & "." _
& eMailSignature("Best Regards", True) & Chr(10) & Chr(10) & Chr(13)
_
& DownloadMessage("snp")




If strMail = "Null" Or Len(strMail) = 0 Or _
DLookup("[MailFlag]", "tblAdminSetup") = False Then
Exit Sub
End If


msgTitle = "E-Mail"
msgBtns = vbYes + vbQuestion + vbDefaultButton2 + vbApplicationModal
msgPmt = " Create E-mail "
msgResp = MsgBox(msgPmt, msgBtns, msgTitle)
If msgResp = vbCancel Then
Exit Sub
Else
blEditMail = IIf(msgResp = vbYes, False, True)
End If

DoCmd.SendObject acSendReport, Me.Name, acFormatSNP, strMail, , ,
"Your Invoice " & IIf(Len(strHorse) > 0, " / " & strHorse, ""), _
strBodyMsg, blEditMail



Exit Sub

If MsgBox("Do you want to send Email??", vbYesNo +
vbDefaultButton2) = vbYes Then

DoCmd.SendObject acSendReport, Me.Name, acFormatRTF,
strMail, , , _
"Your Invoice", strBodyMsg, True
End If


Exit Sub

Error_Handler:
Select Case Err.Number
Case 2501
Exit Sub
Case 2487
Resume Next
Case Else
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description, , "Untrapped Error"

End Select

End Sub
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Vance

Thanks Douglas Great :)
Regards Bob

Douglas J. Steele said:
You can simplify that to

Private Sub cmdEmail_Click()

If Len(lstModify.value & vbNullString) = 0Then
MsgBox "Please Select Invoice.", _
vbApplicationModal + vbOKOnly + vbInformation
Else
DoCmd.OpenReport "rptInvoiceModify", acViewPreview
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bob Vance said:
Douglas, can I get around it by adding my de-activate code to my control
button that is for opening report for emailing...Thanks for your help Bob

Private Sub cmdEmail_Click()
If lstModify.value = "" Or IsNull(lstModify.value) Then
MsgBox "Please Select Invoice.", vbApplicationModal + vbOKOnly +
vbInformation
Exit Sub
End If


DoCmd.OpenReport "rptInvoiceModify", acViewPreview

End Sub

Bob Vance said:
Thanks Douglas for your time, Brilliant just one thing usally when i
close my report by using the Esc key if I do this now I am getting the
error "
Error Number 3509
Description:Operation canceled by user
But if I right click close everthing works fine
Regards Bob

CurrentDb.Execute "UPDATE tblOwnerInfo " & _
"SET Emaildate = Now() " & _
"WHERE OwnerID = " & lngId, dbFailOnError

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a code which on deactivate of report if Owner has an email
address the report is emailed, can I add to the code:
Enter today's date to table/field tblOwnerInfo.Emaildate to the same
"OwnerID"
Thanks for any help....Bob

Private Sub Report_Deactivate()


On Error GoTo Error_Handler

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, dtInvDate As Date, varInvNum As Variant, _
idHorse As Long, strHorse As String


Dim msgPmt As String, msgBtns As Integer, msgTitle As String,
msgResp As Integer

If CurrentProject.AllForms("frmModify").IsLoaded = True Then
lngID = DLookup("OwnerID", "tblInvoice", "InvoiceID = " _
& Form_frmModify.lstModify.Column(0))
ElseIf CurrentProject.AllForms("frmModifyInvoiceClient").IsLoaded =
True Then
lngID = DLookup("OwnerID", "tblInvoice", "InvoiceID = " _
& Form_frmModifyInvoiceClient.lstModify.value)
Else
Exit Sub
End If


strMail = Nz(DLookup("Email", "tblOwnerInfo", "OwnerID = " &
lngID), "")


If Not IsEmailOn Or Not IsOwnerWithEmail(lngID) Then
Exit Sub
End If


dtInvDate = Me.tbInvoiceDate
varInvNum = Me.tbInvoiceNumber
idHorse = Nz(Me.tbHorseID, 0)
If idHorse <> 0 Then
strHorse = Nz(DLookup("[Name]", "qryHorseNameAll", "[HorseID]="
& idHorse), "")
Else
strHorse = ""
End If

strBodyMsg = "Dear "
strBodyMsg = strBodyMsg & Nz(DLookup("[ClientTitle]",
"tblOwnerInfo", "[OwnerID]=" & lngID), " ") & " "
strBodyMsg = strBodyMsg & Nz(DLookup("[OwnerLastName]",
"tblOwnerInfo", "[OwnerID]=" & lngID), " Owner")
strBodyMsg = strBodyMsg & "," & Chr(10) & Chr(10) & Chr(13) _
& "Attached is your " & varInvNum & " Dated " & Format(dtInvDate,
"d-mmm-yyyy") _
& IIf(Len(strHorse) > 0, " for " & strHorse, "") & "." _
& eMailSignature("Best Regards", True) & Chr(10) & Chr(10) &
Chr(13) _
& DownloadMessage("snp")




If strMail = "Null" Or Len(strMail) = 0 Or _
DLookup("[MailFlag]", "tblAdminSetup") = False Then
Exit Sub
End If


msgTitle = "E-Mail"
msgBtns = vbYes + vbQuestion + vbDefaultButton2 +
vbApplicationModal
msgPmt = " Create E-mail "
msgResp = MsgBox(msgPmt, msgBtns, msgTitle)
If msgResp = vbCancel Then
Exit Sub
Else
blEditMail = IIf(msgResp = vbYes, False, True)
End If

DoCmd.SendObject acSendReport, Me.Name, acFormatSNP, strMail, , ,
"Your Invoice " & IIf(Len(strHorse) > 0, " / " & strHorse, ""), _
strBodyMsg, blEditMail



Exit Sub

If MsgBox("Do you want to send Email??", vbYesNo +
vbDefaultButton2) = vbYes Then

DoCmd.SendObject acSendReport, Me.Name, acFormatRTF,
strMail, , , _
"Your Invoice", strBodyMsg, True
End If


Exit Sub

Error_Handler:
Select Case Err.Number
Case 2501
Exit Sub
Case 2487
Resume Next
Case Else
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description, , "Untrapped Error"

End Select

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Emailing Question from my DB 2
Adding a CC to email code! 7
True or False for SNP or PDF format to email 12
Sending Report via Email 3
No Error Message wanted 2
Open Report while creating email 1
Add to text box String 4
Case strFormat = acFormatRTF, Problem 5

Top