Emailing Question from my DB

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

Presently I have a this code to email my Report when I close it, Is there
any way that I can code my Batch Report to email remembering each report may
have a different email address??
Below Is my Print Batch Invoice & My emailing code for single Report
-------------------------------------------
Case "PrintInvoiceBatch"
If IsNull(tbDateFrom.value) Or tbDateFrom.value = "" Or
IsNull(tbDateTo.value) Or tbDateTo.value = "" Then
MsgBox "Please Enter the Begining Date and End Date.",
vbApplicationModal + vbInformation + vbOKOnly
Exit Sub
End If
Me.Visible = False
DoCmd.OpenReport "rptInvoiceBatch", acViewPreview, , , ,
"PrintInvoiceBatch"

---------------------------------------------------------------
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

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


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

strBodyMsg = "To: "
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) _
& "Please find attached 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("PDF")






msgTitle = "E-Mail Sender"
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
Dim strFormat As String

Select Case Me.tbEmailOption.value

Case "ADOBE"
strFormat = acFormatPDF
Case "WORD"
strFormat = acFormatRTF
Case "SNAPSHOT"
strFormat = acFormatSNP
Case "TEXT"
strFormat = acFormatTXT
Case "HTML"
strFormat = acFormatHTML
Case Else
strFormat = acFormatHTML
End Select


DoCmd.SendObject acSendReport, Me.Name, strFormat, To:=strMail,
Cc:=DLookup("EmailCC", "tblOwnerInfo", "OwnerID = " & lngID),
Bcc:=DLookup("EmailBCC", "tblOwnerInfo", "OwnerID = " & lngID), _
Subject:="Your Invoice" & IIf(Len(strHorse) > 0, " / " & strHorse, ""),
MessageText:=strBodyMsg, EditMessage:=blEditMail

Exit Sub

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

DoCmd.SendObject acSendReport, Me.Name, strFormat, strMail,
, , _
"Your Invoice", strBodyMsg, True '
DoCmd.Close acReport, "rptInvoiceModifyEmail", acSaveNo


End If


Exit Sub

Error_Handler:
Select Case Err.Number
Case 2501
Exit Sub
Case 2487
Resume Next
Case Else

End Select
End Sub
 
Bob said:
Below Is my Print Batch Invoice & My emailing code for single Report
Click to expand...

I didn't get far attempting to understand your code. Consider a
different approach for your "If IsNull ..." condition:

If Len(Me.TextBox1 & vbNullString) = 0 _
Or Len(Me.TextBox2 & vbNullString) = 0 Then

That If condition will evaluate as True if either text box is Null or a
zero length string.

Good luck,
Hans
 
You usually approach this by looping thru a recordset and creating and
sending each report.

Here is an example:
http://www.rptsoftware.com/products/email/

--
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com

Bob Vance said:
Presently I have a this code to email my Report when I close it, Is there
any way that I can code my Batch Report to email remembering each report
may have a different email address??
Below Is my Print Batch Invoice & My emailing code for single Report
-------------------------------------------
Case "PrintInvoiceBatch"
If IsNull(tbDateFrom.value) Or tbDateFrom.value = "" Or
IsNull(tbDateTo.value) Or tbDateTo.value = "" Then
MsgBox "Please Enter the Begining Date and End Date.",
vbApplicationModal + vbInformation + vbOKOnly
Exit Sub
End If
Me.Visible = False
DoCmd.OpenReport "rptInvoiceBatch", acViewPreview, , , ,
"PrintInvoiceBatch"

---------------------------------------------------------------
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

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


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

strBodyMsg = "To: "
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) _
& "Please find attached 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("PDF")






msgTitle = "E-Mail Sender"
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
Dim strFormat As String

Select Case Me.tbEmailOption.value

Case "ADOBE"
strFormat = acFormatPDF
Case "WORD"
strFormat = acFormatRTF
Case "SNAPSHOT"
strFormat = acFormatSNP
Case "TEXT"
strFormat = acFormatTXT
Case "HTML"
strFormat = acFormatHTML
Case Else
strFormat = acFormatHTML
End Select


DoCmd.SendObject acSendReport, Me.Name, strFormat, To:=strMail,
Cc:=DLookup("EmailCC", "tblOwnerInfo", "OwnerID = " & lngID),
Bcc:=DLookup("EmailBCC", "tblOwnerInfo", "OwnerID = " & lngID), _
Subject:="Your Invoice" & IIf(Len(strHorse) > 0, " / " & strHorse, ""),
MessageText:=strBodyMsg, EditMessage:=blEditMail

Exit Sub

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

DoCmd.SendObject acSendReport, Me.Name, strFormat, strMail,
, , _
"Your Invoice", strBodyMsg, True '
DoCmd.Close acReport, "rptInvoiceModifyEmail", acSaveNo


End If


Exit Sub

Error_Handler:
Select Case Err.Number
Case 2501
Exit Sub
Case 2487
Resume Next
Case Else

End Select
End Sub
Click to expand...
 
Back
Top