True or False for SNP or PDF format to email

Bob Vance · Jun 20, 2009

Basically I have PDF format for my emails, but is it possible that on my
form
frmCompanyInfo I can have a true/false check box
[frmCompantInfo.EmailOption]
True = PDF, False SNP
So I would have to change my string to accommodate the option
Below are parts of my strings that "PDF" would have to be changed to I
suppose something like
If frmCompantInfo.EmailOption = 0 "PDF" Else "SNP"

Private Sub Report_Activate()

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 'xxx
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("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

DoCmd.SendObject acSendReport, Me.Name, acFormatPDF, 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, acFormatRTF,
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

Public Function DownloadMessage( _
Optional strFileType As String = "PDF", _
Optional strMessage As String = _
"To open this file you will need Adobe Reader. If you do not have this on
your computer, you are able to download it for FREE at ") _
As String

Dim strLink As String

Select Case strFileType
Case "rtf" 'MSWord
strLink =
"http://www.microsoft.com/downloads/...7-8732-48d5-8689-ab826e7b8fdf&DisplayLang=en "
Case "Snp" 'Snapshot
strLink = "http://support.microsoft.com/kb/175274"
Case "PDF" 'Adobe Acrobat
strLink = "http://www.adobe.com"
Case "XLS" 'Excel
strLink =
"http://www.microsoft.com/downloads/...f4-996c-4569-b547-75edbd03aaf0&displaylang=EN"
Case Else
DownloadMessage = ""
Exit Function
End Select

DownloadMessage = Chr(13) _
& strMessage & Chr(10) & Chr(10) & Chr(13) & strLink _
& Chr(10) & Chr(13) & Chr(13) & Chr(13) & Chr(13) & Chr(13) &
Chr(13) & Chr(13) & Chr(13) & Chr(13) & Chr(13) &
"================================================================"

End Function

Private Sub SendMailButton_Click()

On Error GoTo ErrorHandler

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, sndReport As String, strCompany As String
'*****JK: Added 17/10/06
Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp As
Integer

Select Case Me.OpenArgs

Case "OwnerStatement"

sndReport = "rptOwnerPaymentMethod"

lngID = Nz(Me.cbOwnerName.Column(0), 0)
strMail = OwnerEmailAddress(lngID)

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 Statement, Dated" & " " & Format(Date,
"d-mmm-yyyy") & eMailSignature("Best Regards", True) & Chr(10) & Chr(10) &
DownloadMessage("PDF") _

CurrentDb.Execute "UPDATE tblOwnerInfo " & _
"SET EmailDateState = Now() " & _
"WHERE OwnerID = " & lngID, dbFailOnError
'DoCmd.SendObject acSendReport, sndReport, acFormatPDF, strMail, , , "Your
Statement" & " " & "/" & " " & Nz(DLookup("[CompanyName]",
"tblCompanyInfo")), strBodyMsg, True

DoCmd.SendObject acSendReport, sndReport, acFormatPDF, strMail,
Cc:=DLookup("EmailCC", "tblOwnerInfo", "OwnerID = " & lngID),
Bcc:=DLookup("EmailBCC", "tblOwnerInfo", "OwnerID = " & lngID), _
Subject:="Your Statement" & " / " & Nz(DLookup("[CompanyName]",
"tblCompanyInfo")), MessageText:=strBodyMsg 'EditMessage:=blEditMail
cbOwnerName.SetFocus

Case Else
Exit Sub

End Select
Exit Sub

ErrorHandler:

msgTitle = "Untrapped Error"
msgBtns = vbExclamation
If Err.Number = 2501 Then
Err.Clear
Exit Sub
End If
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description & Chr(13) & Chr(13) _
& "(frmBillStatement SendMailButton_Click)", msgBtns, msgTitle

End Sub

Bob Vance · Jun 21, 2009

Albert I am getting this error:
But there is a frmCompanyInfo?

Error 2450
Description Stablesize cant find the form 'frmCompanyInfo'referred to in the
macro expression or vb......

Private Sub SendMailButton_Click()

On Error GoTo ErrorHandler

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, sndReport As String, strCompany As String
'*****JK: Added 17/10/06
Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp As
Integer
Dim strFormat As String

If Forms!frmCompanyInfo!ckbEmailOption = True Then
strFormat = acFormatPDF
Else
strFormat = acFormatSNP
End If

Select Case Me.OpenArgs

Case "OwnerStatement"

sndReport = "rptOwnerPaymentMethod"

lngID = Nz(Me.cbOwnerName.Column(0), 0)
strMail = OwnerEmailAddress(lngID)

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 Statement, Dated" & " " & Format(Date,
"d-mmm-yyyy") & eMailSignature("Best Regards", True) & Chr(10) & Chr(10) &
DownloadMessage("PDF") _

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

DoCmd.SendObject acSendReport, sndReport, strFormat, strMail,
Cc:=DLookup("EmailCC", "tblOwnerInfo", "OwnerID = " & lngID),
Bcc:=DLookup("EmailBCC", "tblOwnerInfo", "OwnerID = " & lngID), _
Subject:="Your Statement" & " / " & Nz(DLookup("[CompanyName]",
"tblCompanyInfo")), MessageText:=strBodyMsg 'EditMessage:=blEditMail
cbOwnerName.SetFocus

Case Else
Exit Sub

End Select
Exit Sub

ErrorHandler:

msgTitle = "Untrapped Error"
msgBtns = vbExclamation
If Err.Number = 2501 Then
Err.Clear
Exit Sub
End If
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description & Chr(13) & Chr(13) _
& "(frmBillStatement SendMailButton_Click)", msgBtns, msgTitle

End Sub

Bob Vance · Jun 21, 2009

Thanks Albert fix it, put an Invisible text box on my report and made a text
field in the table with 2 drop down options in a combo box

Regards Bob
ADOBE and SNAPSHOT

If Me.tbEmailOption.value = "ADOBE" Then
strFormatEmail = acFormatPDF
Else
strFormatEmail = acFormatSNP

End If

Bob Vance said:
Albert I am getting this error:
But there is a frmCompanyInfo?

Error 2450
Description Stablesize cant find the form 'frmCompanyInfo'referred to in
the macro expression or vb......

Private Sub SendMailButton_Click()

On Error GoTo ErrorHandler

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, sndReport As String, strCompany As String
'*****JK: Added 17/10/06
Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp
As Integer
Dim strFormat As String

If Forms!frmCompanyInfo!ckbEmailOption = True Then
strFormat = acFormatPDF
Else
strFormat = acFormatSNP
End If

Select Case Me.OpenArgs

Case "OwnerStatement"

sndReport = "rptOwnerPaymentMethod"

lngID = Nz(Me.cbOwnerName.Column(0), 0)
strMail = OwnerEmailAddress(lngID)

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 Statement, Dated" & " " & Format(Date,
"d-mmm-yyyy") & eMailSignature("Best Regards", True) & Chr(10) & Chr(10) &
DownloadMessage("PDF") _

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

DoCmd.SendObject acSendReport, sndReport, strFormat, strMail,
Cc:=DLookup("EmailCC", "tblOwnerInfo", "OwnerID = " & lngID),
Bcc:=DLookup("EmailBCC", "tblOwnerInfo", "OwnerID = " & lngID), _
Subject:="Your Statement" & " / " & Nz(DLookup("[CompanyName]",
"tblCompanyInfo")), MessageText:=strBodyMsg 'EditMessage:=blEditMail
cbOwnerName.SetFocus

Case Else
Exit Sub

End Select
Exit Sub

ErrorHandler:

msgTitle = "Untrapped Error"
msgBtns = vbExclamation
If Err.Number = 2501 Then
Err.Clear
Exit Sub
End If
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description & Chr(13) & Chr(13) _
& "(frmBillStatement SendMailButton_Click)", msgBtns, msgTitle

End Sub

Albert D. Kallal said:

You could go:

dim strFormat as string

if forms!frmCompantInfo.EmailOption = true then
strformat = acFormatPDF
else
strformat = acFormatSNP
end if

...code...

DoCmd.SendObject acSendReport, Me.Name, strFormat.........

Click to expand...

Albert D. Kallal · Jun 21, 2009

Bob Vance said:
Albert I am getting this error:
But there is a frmCompanyInfo?

How would I know??? It would be the absolute most amazing thing on the
planet if I knew the names of the forms involved without being present and
not seeing the application!!! I wish I really could read peoples minds and
become clairvoyant. Since I am not, then if I used the wrong name, simply
replace with the actual name of the forms being used (it is really
immaterial here...is it not???).

You obviously have to replace the my sample "air code" with the correct form
name that has the control with the check box.

If the code is running in the "current" form, then you actually can drop the
forms qualifier. In this case the you don't need to include the forms name,
and you just simply include the current forms control name (with that check
box) and use "me" which always refers to the code running in the current
form. Thus, when the code is running in a given form's code module, then you
can drop the forms qualifier and use "me". You can thus use:

if me.NameOfCheckBoxContorlGoesHere = true then

bla bla....

Bob Vance · Jun 21, 2009

Sorry Albert , your code was correct, just that i couldnt get the true/false
thing to happen , so I used "text" and that worked with your code, thanks
Bob Vance

Bob Vance · Jun 21, 2009

And I changed it to to the current form because I could not get any
relationship with another form " frmCompanyInfo'..regards Bob Vance

Bob Vance · Jun 21, 2009

Albert is it possible to add a third argument to the other 2
arguments!..Thanks Bob
"SNAPSHOT" is the text for FormatSNP
If Me.tbEmailOption.value = "WORD" Then
strFormat = acFormat

If Me.tbEmailOption.value = "ADOBE" Then
strFormat = acFormatPDF
Else
strFormat = acFormatSNP

Albert D. Kallal · Jun 22, 2009

Bob Vance said:
Albert is it possible to add a third argument to the other 2
arguments!..Thanks Bob
"SNAPSHOT" is the text for FormatSNP

It not clear what you want to do with the 3rd argument?

Perhaps you looking to set a value if all the 3 fail?

You can use:
However, for a "better" code example, you can/should use a case statement.

select case me.tblEmailOption.Value

case "ADOBE"
strFormat = acFormatPDF
case "WORD"
strFormat = acFormatRTF
case "SNAPSHOT"
strFormat = acFormatRTF
case else ' catch all others
strFormat = acFormatSNP
end select

The above would mean that if the email option is not set, then it would
default/use snap. So, you can use a "case else" as a catch all when the
email option is not set.

If you using the other suggested code (and not case select), then simply go:

Dim strFormat As String

strFormat = acFormatSNP
' above sets format to snapshot, and if all fail
' below, then above becoms the default

If Me.tbEmailOption.value = "ADOBE" Then
strFormat = acFormatPDF
end if

If Me.tbEmailOption.value = "WORD" Then
strFormat = acFormatRTF
end if

If Me.tbEmailOption.value = "SNAPSHOT" Then
strFormat = acFormatSNP
End If

So, you have as many ways as doing this as painting a picture. It just not
quite clear what you mean by adding a 3rd parameter??? We could add another
type of export to the above (for example "text") to the above for text, and
simply add:

If Me.tbEmailOption.value = "TEXT" Then
strFormat = acFormatTXT
End If

As mentioned, I think the "case" code is cleaner and easier to read then the
"if then" example, but either approach should work ok.

Do feel free to expand on your question if I misunderstood your question...

Bob Vance · Jun 22, 2009

Thanks Albert for your help, Wonderful and I am going to add TEXT as well
BRILLIANT....BOB

Bob Vance · Jun 23, 2009

Albert I noticed one thing If my email client is not open then access will
only let you send 1 email to outbox then it stalls.. Is that
correct....Regards Bob

Bob Vance · Jun 23, 2009

Also Albert if I change from say SNP to PDF I have to close the form and
re-open it for it to change format!.....Regards Bob

Albert D. Kallal · Jun 23, 2009

Bob Vance said:
Also Albert if I change from say SNP to PDF I have to close the form and
re-open it for it to change format!.....Regards Bob

If your button that launches the reprot is on the SAME form as where the
data is, then we have to force a disk write (since the form's data has not
beeen writen to disk, but the report is driven from the data in the table).

So, if a "email" button is on this form, then we need to "force" the data to
disk.

The code would be:

if me.dirty = True then
me.Dirty = false
end if

' you code to email goes here....

however, if that email button is else where ( in a differnt form), then have
to force the disk write in that code. Hence you go:

if forms![name of above form].Dirty = true then
forms![name of above form].Dirty = false
end if

You code here:

It not 100% clear where/how your code is being launched to send the email,
but you should not need to close the form and re-open it (set the me.Dirty =
false) to force the disk write. This will ensure the table data is updated.
I not sure why the combo box needs to be re-fresh (the combo is on the same
form? -- it should not matter if the combo box is bound or not in this
case).

And, yes...I usually find that having outlook running before you attempt
this works far better...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)

Make

Bob Vance · Jun 24, 2009

Thanks Albert worked a Treat

..Regards Bob

Albert D. Kallal said:
Bob Vance said:

Also Albert if I change from say SNP to PDF I have to close the form and
re-open it for it to change format!.....Regards Bob

Click to expand...

If your button that launches the reprot is on the SAME form as where the
data is, then we have to force a disk write (since the form's data has not
beeen writen to disk, but the report is driven from the data in the
table).

So, if a "email" button is on this form, then we need to "force" the data
to disk.

The code would be:

if me.dirty = True then
me.Dirty = false
end if

' you code to email goes here....

however, if that email button is else where ( in a differnt form), then
have to force the disk write in that code. Hence you go:

if forms![name of above form].Dirty = true then
forms![name of above form].Dirty = false
end if

You code here:

It not 100% clear where/how your code is being launched to send the email,
but you should not need to close the form and re-open it (set the me.Dirty
= false) to force the disk write. This will ensure the table data is
updated. I not sure why the combo box needs to be re-fresh (the combo is
on the same form? -- it should not matter if the combo box is bound or not
in this case).

And, yes...I usually find that having outlook running before you attempt
this works far better...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)

Make