Exporting Report from Access into PDF

[Erick C]

Absolutely, Customer ID (AcctNum) can be sued to identify the reports.

Erick,

Okay... now the we need to find a way to identify the report for each
Customer...  I suggested using the CustomerID is that an option for you?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Gina -
Sorry, I had an error in my code.  I fixed it and the code now creates
individual pdf files for each customer.  The file is named
"Sweeps_Report_For_Email000.pdf" and "Sweeps_Report_For_Email001.pdf"

Good morning Gina!
AcctNum is a text field.

Erick,
Is it looking at a text field or a numeric field?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
I am not the biggest fan of Lotus Notes myself, this would be so much
easier if I was using Outlook. Regarding your email code question,
yes you are correct. The code as it is written emails only one
address at a time. I had guessed that it would be better and easier
to integrate into another code. I also added a list box to my form
that pulls in the customer ID (AcctNum), customer name, and email
address.
I thought that the same list box could be used to pull an email
address right after it was used to create and name the pdf. So, the
identifying portion of the name of the file would come from the
customer ID column in the list box, then the email address from the
email section of the list box. SO basically right after a pdf is made
it is emailed and then it moves on to the next customer ID in the list
box. Does my weird thought process make any sense at all, or was I
way off?
OK, I have changed the code attached to the button, but I have
received an error saying Compile error: can't find project or
library. The debugger highlights Format at the beginning of "Format
(intCounter,"000")"
Here is what my code looks like, did I add something that I was not
supposed to?
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![CustomerListBox]
For intCounter = 0 To CustomerListBox.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test
\Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf", False,
False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
Erick,
First, the report has to drop out to individual files, one way or the
other... So whether you use a combo box or a list box doesn't matter.
But
it must loop thru and attach a number or something on to the end of
each
file so it knows the reports are seperate entities. Might be betterto
pick
to pick the CustomerID as the report name so when eMailing the eMail
knows
which report to grab. (I can modify to include CustomerID.)
'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![YourComboBox]
For intCounter = 0 To Combo0.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[YourFieldControlSource] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Report1", ,
"DriveLetter\FolderName\Sweeps_Report_For_Email" & Format(intCounter,
"000")
& ".pdf", False, False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
The code you supplied below appears to go to one eMail address at a
time...
Is this true? I don't have Lotus Notes so no way to test.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
The email addresses are not actually part of a combo box on my form, I
just have them all in a Customers_To_Email table. The field is "Email
Address" in the table.
I need to somehow set up a loop with the code so an individual report
is created for each customer in the table. The test PDF that was
created has all customers in it, which would not be too cool at all..
I also have a working code for emailing out of Lotus Notes, though I
do not know how to integrate it into the PDF code, but maybe it can
help.
Dim s As Object
Dim db As Object
Dim doc As Object
Dim rtItem As Object
Dim Server As String, Database As String
Dim strError As String
'startup Lotus notes and get object handle
Set s = CreateObject("Notes.notesSession")
Server = s.GETENVIRONMENTSTRING("MailServer", True)
Database = s.GETENVIRONMENTSTRING("MailFile", True)
Set db = s.GetDatabase(Server, Database)
On Error GoTo ErrorLogon
'see if user is logged on
Set doc = db.CreateDocument
On Error GoTo 0
doc.Form = "Memo"
doc.importance = "1" '(Where 1=Urgent, 2= Normal, 3= FYI)
'Send an e-mail to
doc.sendTO = "email address goes here"
doc.RETURNRECEIPT = "1"
doc.Subject = "Test Email"
Set rtItem = doc.CreateRichTextItem("Body")
Call rtItem.AppendText("Test Email")
Call rtItem.AddNewLine(2)
Call rtItem.AppendText("Dear sir or madam")
Call rtItem.AddNewLine(1)
Call rtItem.AppendText("This is a test email for the Treasury
Department. If you have received this in error, please disregard and
delete. ")
Call rtItem.AddNewLine(2)
Call rtItem.EMBEDOBJECT(1454, "", "G:\treas\Erick\Sweep Test Docs
\rates.xls")
doc.SaveMessageOnSend = True
doc.PostedDate = Now() 'Gets the mail to appear in the sent items
folder
Call doc.Send(False)
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Set rtItem = Nothing
ErrorLogon:
If Err.Number = 7063 Then
MsgBox " You must first logon to Lotus Notes"
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Set rtItem = Nothing
End If
Refresh
MsgBox " Your email has been sent"
Erick,
Okay, one more thing... the combo box that has your eMail addresses
in
it...
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
OK, the button opened up the report in preview view in access and
created a pdf file in my test folder (I modified the path in your
code) named MyTestFile.pdf, and the adobe viewer opened up.
Erick,
Then put this line behind a button
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview
Call ConvertReportToPDF("Sweeps_Report_For_Email", ,
"c:\MyTestFile.pdf",
False)
and let me know what the results are. Once we get this working I
will
incorporate into other peice of code.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
I have the module in my database. I have double checked that my
defauly printer is working, everything is printing out of the
database
OK. Double checked to make sure it was not set up to the Adobe PDF
writer.
My report is "Sweeps_Report_For_Email"
On Feb 2, 1:44 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Step 1 copy the module into your database and then type here the
name
of
your report. Also make sure you have a default printer assigned
to
windows.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gnia -
I apologize for the

...

read more »- Hide quoted text -

- Show quoted text -

 

[Erick C]

Gina -
OK, I have in my combo box a field for [AcctNum] and [Email Address].
The AcctNum field is a unique identifier for each customer account,
there are absolutely no duplicate records ever. It is a text field
since the number is not solely numeric.
Let me know if I need to put the info somewhere other than the combo
box that is already being used.

Absolutely, Customer ID (AcctNum) can be sued to identify the reports.

Erick,

Okay... now the we need to find a way to identify the report for each
Customer...  I suggested using the CustomerID is that an option for you?

"I feel I have been denied critical, need to know, information!" - Tremors
II

"Erick C" <[email protected]> wrote in message

Gina -
Sorry, I had an error in my code.  I fixed it and the code now creates
individual pdf files for each customer.  The file is named
"Sweeps_Report_For_Email000.pdf" and "Sweeps_Report_For_Email001.pdf"

Good morning Gina!
AcctNum is a text field.
Erick,
Is it looking at a text field or a numeric field?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
I am not the biggest fan of Lotus Notes myself, this would be so much
easier if I was using Outlook. Regarding your email code question,
yes you are correct. The code as it is written emails only one
address at a time. I had guessed that it would be better and easier
to integrate into another code. I also added a list box to my form
that pulls in the customer ID (AcctNum), customer name, and email
address.
I thought that the same list box could be used to pull an email
address right after it was used to create and name the pdf. So, the
identifying portion of the name of the file would come from the
customer ID column in the list box, then the email address from the
email section of the list box. SO basically right after a pdf is made
it is emailed and then it moves on to the next customer ID in the list
box. Does my weird thought process make any sense at all, or was I
way off?
OK, I have changed the code attached to the button, but I have
received an error saying Compile error: can't find project or
library. The debugger highlights Format at the beginning of "Format
(intCounter,"000")"
Here is what my code looks like, did I add something that I was not
supposed to?
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![CustomerListBox]
For intCounter = 0 To CustomerListBox.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test
\Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf", False,
False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
Erick,
First, the report has to drop out to individual files, one way orthe
other... So whether you use a combo box or a list box doesn't matter.
But
it must loop thru and attach a number or something on to the end of
each
file so it knows the reports are seperate entities. Might be better to
pick
to pick the CustomerID as the report name so when eMailing the eMail
knows
which report to grab. (I can modify to include CustomerID.)
'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![YourComboBox]
For intCounter = 0 To Combo0.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[YourFieldControlSource] = '" & cboCode.ItemData(intCounter) &"'"
DoEvents
Call ConvertReportToPDF("Report1", ,
"DriveLetter\FolderName\Sweeps_Report_For_Email" & Format(intCounter,
"000")
& ".pdf", False, False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
The code you supplied below appears to go to one eMail address ata
time...
Is this true? I don't have Lotus Notes so no way to test.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
The email addresses are not actually part of a combo box on my form, I
just have them all in a Customers_To_Email table. The field is "Email
Address" in the table.
I need to somehow set up a loop with the code so an individual report
is created for each customer in the table. The test PDF that was
created has all customers in it, which would not be too cool at all.
I also have a working code for emailing out of Lotus Notes, though I
do not know how to integrate it into the PDF code, but maybe it can
help.
Dim s As Object
Dim db As Object
Dim doc As Object
Dim rtItem As Object
Dim Server As String, Database As String
Dim strError As String
'startup Lotus notes and get object handle
Set s = CreateObject("Notes.notesSession")
Server = s.GETENVIRONMENTSTRING("MailServer", True)
Database = s.GETENVIRONMENTSTRING("MailFile", True)
Set db = s.GetDatabase(Server, Database)
On Error GoTo ErrorLogon
'see if user is logged on
Set doc = db.CreateDocument
On Error GoTo 0
doc.Form = "Memo"
doc.importance = "1" '(Where 1=Urgent, 2= Normal, 3= FYI)
'Send an e-mail to
doc.sendTO = "email address goes here"
doc.RETURNRECEIPT = "1"
doc.Subject = "Test Email"
Set rtItem = doc.CreateRichTextItem("Body")
Call rtItem.AppendText("Test Email")
Call rtItem.AddNewLine(2)
Call rtItem.AppendText("Dear sir or madam")
Call rtItem.AddNewLine(1)
Call rtItem.AppendText("This is a test email for the Treasury
Department. If you have received this in error, please disregard and
delete. ")
Call rtItem.AddNewLine(2)
Call rtItem.EMBEDOBJECT(1454, "", "G:\treas\Erick\Sweep Test Docs
\rates.xls")
doc.SaveMessageOnSend = True
doc.PostedDate = Now() 'Gets the mail to appear in the sent items
folder
Call doc.Send(False)
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Set rtItem = Nothing
ErrorLogon:
If Err.Number = 7063 Then
MsgBox " You must first logon to Lotus Notes"
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Set rtItem = Nothing
End If
Refresh
MsgBox " Your email has been sent"
Erick,
Okay, one more thing... the combo box that has your eMail addresses
in
it...
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
OK, the button opened up the report in preview view in access and
created a pdf file in my test folder (I modified the path in your
code) named MyTestFile.pdf, and the adobe viewer opened up.
Erick,
Then put this line behind a button
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview
Call ConvertReportToPDF("Sweeps_Report_For_Email", ,
"c:\MyTestFile.pdf",
False)
and let me know what the results are. Once we get this working I
will
incorporate into other peice of code.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
I have the module in my database. I have double checked that my
defauly printer is working, everything is printing out of the
database
OK. Double checked to make sure it was not set up to the Adobe PDF
writer.
My report is "Sweeps_Report_For_Email"
On Feb 2, 1:44 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Step 1 copy the module into your database and then type here the
name
of
your

...

read more »- Hide quoted text -

- Show quoted text -

 

[Gina Whipp]

Erick,

Then adjust the line to say...

Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test\" &
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False, False)

I added the dash so the code can gleam the AccNum which we can tell it
before the dash...

Now, I have some code to loops thru Lotus Notes and send a PDF report, only
as I don't have Lotus Notes I have no way to test or even play to modify...

'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()

Dim user As String, server As String, mailfile As String
Dim doc As Object, rtobject As Object
Dim session As Object, db As Object
Dim item As Object, rtitem As Variant
Dim Picture As Object

'Arrays to hold a list of Email Recipients and a list of
'pdf files to be attached to the email
Dim strSendTo() As String
Dim strPDFList() As String
Dim strBodyText As String
Dim strSubject As String
Dim i As Integer

'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")

'Adds all recipients to be emailed to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strSendTo(i)
strSendTo(i) = mrstAccess![To]
mrstAccess.MoveNext
i = i + 1
Loop

strSubject = txtSubject.Text
strBodyText = txtBody.Text

'Creates a Notessession Object and provides mail details
Set session = CreateObject("notes.notessession")
user = session.UserName
server = session.GETENVIRONMENTSTRING("MailServer", True)
mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
Set db = session.GETDATABASE(server, mailfile)
Set doc = db.CREATEDOCUMENT()
doc.Form = "Memo"
doc.sendto = strSendTo()
doc.Subject = strSubject
Set rtitem = doc.CreateRichTextItem("body")
Call rtitem.APPENDTEXT(strBodyText)
Call rtitem.ADDNEWLINE(2)

'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM PDFs")

'Attaches all pdf files specified to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strPDFList(i)
strPDFList(i) = mrstAccess![PDF_Name]
Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
mrstAccess.MoveNext
i = i + 1
Loop

'Send the mail
Call doc.Send(False)
Set doc = Nothing

End Sub
'***End of Code

There is also more information here..
http://www.bigresource.com/VB-Visua...39-s-to-multiple-recipients--qQPm7FnXjQ.html#

Do you think you can modify the above?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Absolutely, Customer ID (AcctNum) can be sued to identify the reports.

Erick,

Okay... now the we need to find a way to identify the report for each
Customer... I suggested using the CustomerID is that an option for you?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Gina -
Sorry, I had an error in my code. I fixed it and the code now creates
individual pdf files for each customer. The file is named
"Sweeps_Report_For_Email000.pdf" and "Sweeps_Report_For_Email001.pdf"

Good morning Gina!
AcctNum is a text field.

Erick,
Is it looking at a text field or a numeric field?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
I am not the biggest fan of Lotus Notes myself, this would be so much
easier if I was using Outlook. Regarding your email code question,
yes you are correct. The code as it is written emails only one
address at a time. I had guessed that it would be better and easier
to integrate into another code. I also added a list box to my form
that pulls in the customer ID (AcctNum), customer name, and email
address.
I thought that the same list box could be used to pull an email
address right after it was used to create and name the pdf. So, the
identifying portion of the name of the file would come from the
customer ID column in the list box, then the email address from the
email section of the list box. SO basically right after a pdf is made
it is emailed and then it moves on to the next customer ID in the list
box. Does my weird thought process make any sense at all, or was I
way off?
OK, I have changed the code attached to the button, but I have
received an error saying Compile error: can't find project or
library. The debugger highlights Format at the beginning of "Format
(intCounter,"000")"
Here is what my code looks like, did I add something that I was not
supposed to?
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![CustomerListBox]
For intCounter = 0 To CustomerListBox.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test
\Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf", False,
False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
Erick,
First, the report has to drop out to individual files, one way or
the
other... So whether you use a combo box or a list box doesn't
matter.
But
it must loop thru and attach a number or something on to the end of
each
file so it knows the reports are seperate entities. Might be better
to
pick
to pick the CustomerID as the report name so when eMailing the eMail
knows
which report to grab. (I can modify to include CustomerID.)
'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![YourComboBox]
For intCounter = 0 To Combo0.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[YourFieldControlSource] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Report1", ,
"DriveLetter\FolderName\Sweeps_Report_For_Email" &
Format(intCounter,
"000")
& ".pdf", False, False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
The code you supplied below appears to go to one eMail address at a
time...
Is this true? I don't have Lotus Notes so no way to test.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
The email addresses are not actually part of a combo box on my form,
I
just have them all in a Customers_To_Email table. The field is
"Email
Address" in the table.
I need to somehow set up a loop with the code so an individual
report
is created for each customer in the table. The test PDF that was
created has all customers in it, which would not be too cool at all.
I also have a working code for emailing out of Lotus Notes, though I
do not know how to integrate it into the PDF code, but maybe it can
help.
Dim s As Object
Dim db As Object
Dim doc As Object
Dim rtItem As Object
Dim Server As String, Database As String
Dim strError As String
'startup Lotus notes and get object handle
Set s = CreateObject("Notes.notesSession")
Server = s.GETENVIRONMENTSTRING("MailServer", True)
Database = s.GETENVIRONMENTSTRING("MailFile", True)
Set db = s.GetDatabase(Server, Database)
On Error GoTo ErrorLogon
'see if user is logged on
Set doc = db.CreateDocument
On Error GoTo 0
doc.Form = "Memo"
doc.importance = "1" '(Where 1=Urgent, 2= Normal, 3= FYI)
'Send an e-mail to
doc.sendTO = "email address goes here"
doc.RETURNRECEIPT = "1"
doc.Subject = "Test Email"
Set rtItem = doc.CreateRichTextItem("Body")
Call rtItem.AppendText("Test Email")
Call rtItem.AddNewLine(2)
Call rtItem.AppendText("Dear sir or madam")
Call rtItem.AddNewLine(1)
Call rtItem.AppendText("This is a test email for the Treasury
Department. If you have received this in error, please disregard and
delete. ")
Call rtItem.AddNewLine(2)
Call rtItem.EMBEDOBJECT(1454, "", "G:\treas\Erick\Sweep Test Docs
\rates.xls")
doc.SaveMessageOnSend = True
doc.PostedDate = Now() 'Gets the mail to appear in the sent items
folder
Call doc.Send(False)
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Set rtItem = Nothing
ErrorLogon:
If Err.Number = 7063 Then
MsgBox " You must first logon to Lotus Notes"
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Set rtItem = Nothing
End If
Refresh
MsgBox " Your email has been sent"
Erick,
Okay, one more thing... the combo box that has your eMail
addresses
in
it...
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
OK, the button opened up the report in preview view in access and
created a pdf file in my test folder (I modified the path in your
code) named MyTestFile.pdf, and the adobe viewer opened up.
On Feb 2, 2:16 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Then put this line behind a button
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview
Call ConvertReportToPDF("Sweeps_Report_For_Email", ,
"c:\MyTestFile.pdf",
False)
and let me know what the results are. Once we get this working I
will
incorporate into other peice of code.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
I have the module in my database. I have double checked that my
defauly printer is working, everything is printing out of the
database
OK. Double checked to make sure it was not set up to the Adobe
PDF
writer.
My report is "Sweeps_Report_For_Email"
On Feb 2, 1:44 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Step 1 copy the module into your database and then type here
the
name
of
your report. Also make sure you have a default printer
assigned
to
windows.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gnia -
I apologize for the

...

read more »- Hide quoted text -

- Show quoted text -

 

[Gina Whipp]

Erick,

Did my reply not show up?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina -
OK, I have in my combo box a field for [AcctNum] and [Email Address].
The AcctNum field is a unique identifier for each customer account,
there are absolutely no duplicate records ever. It is a text field
since the number is not solely numeric.
Let me know if I need to put the info somewhere other than the combo
box that is already being used.

Absolutely, Customer ID (AcctNum) can be sued to identify the reports.

Erick,

Okay... now the we need to find a way to identify the report for each
Customer... I suggested using the CustomerID is that an option for you?

"I feel I have been denied critical, need to know, information!" -
Tremors
II

"Erick C" <[email protected]> wrote in message

Gina -
Sorry, I had an error in my code. I fixed it and the code now creates
individual pdf files for each customer. The file is named
"Sweeps_Report_For_Email000.pdf" and "Sweeps_Report_For_Email001.pdf"

Good morning Gina!
AcctNum is a text field.
Erick,
Is it looking at a text field or a numeric field?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
I am not the biggest fan of Lotus Notes myself, this would be so
much
easier if I was using Outlook. Regarding your email code question,
yes you are correct. The code as it is written emails only one
address at a time. I had guessed that it would be better and easier
to integrate into another code. I also added a list box to my form
that pulls in the customer ID (AcctNum), customer name, and email
address.
I thought that the same list box could be used to pull an email
address right after it was used to create and name the pdf. So, the
identifying portion of the name of the file would come from the
customer ID column in the list box, then the email address from the
email section of the list box. SO basically right after a pdf is
made
it is emailed and then it moves on to the next customer ID in the
list
box. Does my weird thought process make any sense at all, or was I
way off?
OK, I have changed the code attached to the button, but I have
received an error saying Compile error: can't find project or
library. The debugger highlights Format at the beginning of "Format
(intCounter,"000")"
Here is what my code looks like, did I add something that I was not
supposed to?
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![CustomerListBox]
For intCounter = 0 To CustomerListBox.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test
\Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False,
False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
Erick,
First, the report has to drop out to individual files, one way or
the
other... So whether you use a combo box or a list box doesn't
matter.
But
it must loop thru and attach a number or something on to the end
of
each
file so it knows the reports are seperate entities. Might be
better to
pick
to pick the CustomerID as the report name so when eMailing the
eMail
knows
which report to grab. (I can modify to include CustomerID.)
'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![YourComboBox]
For intCounter = 0 To Combo0.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[YourFieldControlSource] = '" & cboCode.ItemData(intCounter) &
"'"
DoEvents
Call ConvertReportToPDF("Report1", ,
"DriveLetter\FolderName\Sweeps_Report_For_Email" &
Format(intCounter,
"000")
& ".pdf", False, False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
The code you supplied below appears to go to one eMail address at
a
time...
Is this true? I don't have Lotus Notes so no way to test.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
The email addresses are not actually part of a combo box on my
form, I
just have them all in a Customers_To_Email table. The field is
"Email
Address" in the table.
I need to somehow set up a loop with the code so an individual
report
is created for each customer in the table. The test PDF that was
created has all customers in it, which would not be too cool at
all.
I also have a working code for emailing out of Lotus Notes, though
I
do not know how to integrate it into the PDF code, but maybe it
can
help.
Dim s As Object
Dim db As Object
Dim doc As Object
Dim rtItem As Object
Dim Server As String, Database As String
Dim strError As String
'startup Lotus notes and get object handle
Set s = CreateObject("Notes.notesSession")
Server = s.GETENVIRONMENTSTRING("MailServer", True)
Database = s.GETENVIRONMENTSTRING("MailFile", True)
Set db = s.GetDatabase(Server, Database)
On Error GoTo ErrorLogon
'see if user is logged on
Set doc = db.CreateDocument
On Error GoTo 0
doc.Form = "Memo"
doc.importance = "1" '(Where 1=Urgent, 2= Normal, 3= FYI)
'Send an e-mail to
doc.sendTO = "email address goes here"
doc.RETURNRECEIPT = "1"
doc.Subject = "Test Email"
Set rtItem = doc.CreateRichTextItem("Body")
Call rtItem.AppendText("Test Email")
Call rtItem.AddNewLine(2)
Call rtItem.AppendText("Dear sir or madam")
Call rtItem.AddNewLine(1)
Call rtItem.AppendText("This is a test email for the Treasury
Department. If you have received this in error, please disregard
and
delete. ")
Call rtItem.AddNewLine(2)
Call rtItem.EMBEDOBJECT(1454, "", "G:\treas\Erick\Sweep Test Docs
\rates.xls")
doc.SaveMessageOnSend = True
doc.PostedDate = Now() 'Gets the mail to appear in the sent items
folder
Call doc.Send(False)
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Set rtItem = Nothing
ErrorLogon:
If Err.Number = 7063 Then
MsgBox " You must first logon to Lotus Notes"
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Set rtItem = Nothing
End If
Refresh
MsgBox " Your email has been sent"
On Feb 2, 3:30 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Okay, one more thing... the combo box that has your eMail
addresses
in
it...
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
OK, the button opened up the report in preview view in access
and
created a pdf file in my test folder (I modified the path in
your
code) named MyTestFile.pdf, and the adobe viewer opened up.
On Feb 2, 2:16 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Then put this line behind a button
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview
Call ConvertReportToPDF("Sweeps_Report_For_Email", ,
"c:\MyTestFile.pdf",
False)
and let me know what the results are. Once we get this working
I
will
incorporate into other peice of code.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
I have the module in my database. I have double checked that
my
defauly printer is working, everything is printing out of the
database
OK. Double checked to make sure it was not set up to the Adobe
PDF
writer.
My report is "Sweeps_Report_For_Email"
On Feb 2, 1:44 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Step 1 copy the module into your database and then type here
the
name
of
your

...

read more »- Hide quoted text -

- Show quoted text -

 

[Erick C]

Hi Gina -
I have changed the Call line, but I received an error saying "Complie
Error: Method or Data Member Not Found " and the debugger highlighted
the ".AcctNum" part of Me.AcctNum.

As far as the email portion goes, I will do my best to try and get it
going. I have run into a snag already with the 'Retrieves pdf file
locations from an Access Database

Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")

What goes into the To and Email Portion?
If I try to run it errors and highlights the above line, the error
says Error 424, Object Required

Erick,

Then adjust the line to say...

Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test\" &
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False, False)

I added the dash so the code can gleam the AccNum which we can tell it
before the dash...

Now, I have some code to loops thru Lotus Notes and send a PDF report, only
as I don't have Lotus Notes I have no way to test or even play to modify....

'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()

    Dim user As String, server As String, mailfile As String
    Dim doc As Object, rtobject As Object
    Dim session As Object, db As Object
    Dim item As Object, rtitem As Variant
    Dim Picture As Object

    'Arrays to hold a list of Email Recipients and a list of
    'pdf files to be attached to the email
    Dim strSendTo() As String
    Dim strPDFList() As String
    Dim strBodyText As String
    Dim strSubject As String
    Dim i As Integer

    'Retrieves pdf file locations from an Access Database
    Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")

    'Adds all recipients to be emailed to the email
    i = 1
    Do While Not mrstAccess.EOF
        ReDim Preserve strSendTo(i)
        strSendTo(i) = mrstAccess![To]
        mrstAccess.MoveNext
        i = i + 1
    Loop

    strSubject = txtSubject.Text
    strBodyText = txtBody.Text

    'Creates a Notessession Object and provides mail details
    Set session = CreateObject("notes.notessession")
    user = session.UserName
    server = session.GETENVIRONMENTSTRING("MailServer", True)
    mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
    Set db = session.GETDATABASE(server, mailfile)
    Set doc = db.CREATEDOCUMENT()
    doc.Form = "Memo"
    doc.sendto = strSendTo()
    doc.Subject = strSubject
    Set rtitem = doc.CreateRichTextItem("body")
    Call rtitem.APPENDTEXT(strBodyText)
    Call rtitem.ADDNEWLINE(2)

    'Retrieves pdf file locations from an Access Database
    Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM PDFs")

    'Attaches all pdf files specified to the email
    i = 1
    Do While Not mrstAccess.EOF
        ReDim Preserve strPDFList(i)
        strPDFList(i) = mrstAccess![PDF_Name]
        Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
        mrstAccess.MoveNext
        i = i + 1
    Loop

    'Send the mail
    Call doc.Send(False)
    Set doc = Nothing

End Sub
'***End of Code

There is also more information here..http://www.bigresource.com/VB-Visual-Basic-to-send-Lotus-Notes-Email-...

Do you think you can modify the above?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Absolutely, Customer ID (AcctNum) can be sued to identify the reports.

Erick,

Okay... now the we need to find a way to identify the report for each
Customer... I suggested using the CustomerID is that an option for you?

"I feel I have been denied critical, need to know, information!" - Tremors
II

"Erick C" <[email protected]> wrote in message

Gina -
Sorry, I had an error in my code. I fixed it and the code now creates
individual pdf files for each customer. The file is named
"Sweeps_Report_For_Email000.pdf" and "Sweeps_Report_For_Email001.pdf"

Good morning Gina!
AcctNum is a text field.
Erick,
Is it looking at a text field or a numeric field?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
I am not the biggest fan of Lotus Notes myself, this would be so much
easier if I was using Outlook. Regarding your email code question,
yes you are correct. The code as it is written emails only one
address at a time. I had guessed that it would be better and easier
to integrate into another code. I also added a list box to my form
that pulls in the customer ID (AcctNum), customer name, and email
address.
I thought that the same list box could be used to pull an email
address right after it was used to create and name the pdf. So, the
identifying portion of the name of the file would come from the
customer ID column in the list box, then the email address from the
email section of the list box. SO basically right after a pdf is made
it is emailed and then it moves on to the next customer ID in the list
box. Does my weird thought process make any sense at all, or was I
way off?
OK, I have changed the code attached to the button, but I have
received an error saying Compile error: can't find project or
library. The debugger highlights Format at the beginning of "Format
(intCounter,"000")"
Here is what my code looks like, did I add something that I was not
supposed to?
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![CustomerListBox]
For intCounter = 0 To CustomerListBox.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test
\Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf", False,
False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
Erick,
First, the report has to drop out to individual files, one way or
the
other... So whether you use a combo box or a list box doesn't
matter.
But
it must loop thru and attach a number or something on to the end of
each
file so it knows the reports are seperate entities. Might be better
to
pick
to pick the CustomerID as the report name so when eMailing the eMail
knows
which report to grab. (I can modify to include CustomerID.)
'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![YourComboBox]
For intCounter = 0 To Combo0.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[YourFieldControlSource] = '" & cboCode.ItemData(intCounter) &"'"
DoEvents
Call ConvertReportToPDF("Report1", ,
"DriveLetter\FolderName\Sweeps_Report_For_Email" &
Format(intCounter,
"000")
& ".pdf", False, False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
The code you supplied below appears to go to one eMail address ata
time...
Is this true? I don't have Lotus Notes so no way to test.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
The email addresses are not actually part of a combo box on my form,
I
just have them all in a Customers_To_Email table. The field is
"Email
Address" in the table.
I need to somehow set up a loop with the code so an individual
report
is created for each customer in the table. The test PDF that was
created has all customers in it, which would not be too cool at all.
I also have a working code for emailing out of Lotus Notes, though I
do not know how to integrate it into the PDF code, but maybe it can
help.
Dim s As Object
Dim db As Object
Dim doc As Object
Dim rtItem As Object
Dim Server As String, Database As String
Dim strError As String
'startup Lotus notes and get object handle
Set s = CreateObject("Notes.notesSession")
Server = s.GETENVIRONMENTSTRING("MailServer", True)
Database = s.GETENVIRONMENTSTRING("MailFile", True)
Set db = s.GetDatabase(Server, Database)
On Error GoTo ErrorLogon
'see if user is logged on
Set doc = db.CreateDocument
On Error GoTo 0
doc.Form = "Memo"
doc.importance = "1" '(Where 1=Urgent, 2= Normal, 3= FYI)
'Send an e-mail to
doc.sendTO = "email address goes here"
doc.RETURNRECEIPT = "1"
doc.Subject = "Test Email"
Set rtItem = doc.CreateRichTextItem("Body")
Call rtItem.AppendText("Test Email")
Call rtItem.AddNewLine(2)
Call rtItem.AppendText("Dear sir or madam")
Call rtItem.AddNewLine(1)
Call rtItem.AppendText("This is a test email for the Treasury
Department. If you have received this in error, please

...

read more »- Hide quoted text -

- Show quoted text -

 

[Gina Whipp]

Erick,

Sorry Me.NameOfAccNumField on your form.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi Gina -
I have changed the Call line, but I received an error saying "Complie
Error: Method or Data Member Not Found " and the debugger highlighted
the ".AcctNum" part of Me.AcctNum.

As far as the email portion goes, I will do my best to try and get it
going. I have run into a snag already with the 'Retrieves pdf file
locations from an Access Database

Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")

What goes into the To and Email Portion?
If I try to run it errors and highlights the above line, the error
says Error 424, Object Required

Erick,

Then adjust the line to say...

Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test\" &
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter, "000") &
".pdf",
False, False)

I added the dash so the code can gleam the AccNum which we can tell it
before the dash...

Now, I have some code to loops thru Lotus Notes and send a PDF report,
only
as I don't have Lotus Notes I have no way to test or even play to
modify...

'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()

Dim user As String, server As String, mailfile As String
Dim doc As Object, rtobject As Object
Dim session As Object, db As Object
Dim item As Object, rtitem As Variant
Dim Picture As Object

'Arrays to hold a list of Email Recipients and a list of
'pdf files to be attached to the email
Dim strSendTo() As String
Dim strPDFList() As String
Dim strBodyText As String
Dim strSubject As String
Dim i As Integer

'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")

'Adds all recipients to be emailed to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strSendTo(i)
strSendTo(i) = mrstAccess![To]
mrstAccess.MoveNext
i = i + 1
Loop

strSubject = txtSubject.Text
strBodyText = txtBody.Text

'Creates a Notessession Object and provides mail details
Set session = CreateObject("notes.notessession")
user = session.UserName
server = session.GETENVIRONMENTSTRING("MailServer", True)
mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
Set db = session.GETDATABASE(server, mailfile)
Set doc = db.CREATEDOCUMENT()
doc.Form = "Memo"
doc.sendto = strSendTo()
doc.Subject = strSubject
Set rtitem = doc.CreateRichTextItem("body")
Call rtitem.APPENDTEXT(strBodyText)
Call rtitem.ADDNEWLINE(2)

'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM PDFs")

'Attaches all pdf files specified to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strPDFList(i)
strPDFList(i) = mrstAccess![PDF_Name]
Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
mrstAccess.MoveNext
i = i + 1
Loop

'Send the mail
Call doc.Send(False)
Set doc = Nothing

End Sub
'***End of Code

There is also more information
here..http://www.bigresource.com/VB-Visual-Basic-to-send-Lotus-Notes-Email-...

Do you think you can modify the above?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Absolutely, Customer ID (AcctNum) can be sued to identify the reports.

Erick,

Okay... now the we need to find a way to identify the report for each
Customer... I suggested using the CustomerID is that an option for you?

"I feel I have been denied critical, need to know, information!" -
Tremors
II

"Erick C" <[email protected]> wrote in message

Gina -
Sorry, I had an error in my code. I fixed it and the code now creates
individual pdf files for each customer. The file is named
"Sweeps_Report_For_Email000.pdf" and "Sweeps_Report_For_Email001.pdf"

Good morning Gina!
AcctNum is a text field.
Erick,
Is it looking at a text field or a numeric field?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
I am not the biggest fan of Lotus Notes myself, this would be so
much
easier if I was using Outlook. Regarding your email code question,
yes you are correct. The code as it is written emails only one
address at a time. I had guessed that it would be better and easier
to integrate into another code. I also added a list box to my form
that pulls in the customer ID (AcctNum), customer name, and email
address.
I thought that the same list box could be used to pull an email
address right after it was used to create and name the pdf. So, the
identifying portion of the name of the file would come from the
customer ID column in the list box, then the email address from the
email section of the list box. SO basically right after a pdf is
made
it is emailed and then it moves on to the next customer ID in the
list
box. Does my weird thought process make any sense at all, or was I
way off?
OK, I have changed the code attached to the button, but I have
received an error saying Compile error: can't find project or
library. The debugger highlights Format at the beginning of "Format
(intCounter,"000")"
Here is what my code looks like, did I add something that I was not
supposed to?
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![CustomerListBox]
For intCounter = 0 To CustomerListBox.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test
\Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False,
False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
Erick,
First, the report has to drop out to individual files, one way or
the
other... So whether you use a combo box or a list box doesn't
matter.
But
it must loop thru and attach a number or something on to the end
of
each
file so it knows the reports are seperate entities. Might be
better
to
pick
to pick the CustomerID as the report name so when eMailing the
eMail
knows
which report to grab. (I can modify to include CustomerID.)
'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![YourComboBox]
For intCounter = 0 To Combo0.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[YourFieldControlSource] = '" & cboCode.ItemData(intCounter) &
"'"
DoEvents
Call ConvertReportToPDF("Report1", ,
"DriveLetter\FolderName\Sweeps_Report_For_Email" &
Format(intCounter,
"000")
& ".pdf", False, False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
The code you supplied below appears to go to one eMail address at
a
time...
Is this true? I don't have Lotus Notes so no way to test.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
The email addresses are not actually part of a combo box on my
form,
I
just have them all in a Customers_To_Email table. The field is
"Email
Address" in the table.
I need to somehow set up a loop with the code so an individual
report
is created for each customer in the table. The test PDF that was
created has all customers in it, which would not be too cool at
all.
I also have a working code for emailing out of Lotus Notes, though
I
do not know how to integrate it into the PDF code, but maybe it
can
help.
Dim s As Object
Dim db As Object
Dim doc As Object
Dim rtItem As Object
Dim Server As String, Database As String
Dim strError As String
'startup Lotus notes and get object handle
Set s = CreateObject("Notes.notesSession")
Server = s.GETENVIRONMENTSTRING("MailServer", True)
Database = s.GETENVIRONMENTSTRING("MailFile", True)
Set db = s.GetDatabase(Server, Database)
On Error GoTo ErrorLogon
'see if user is logged on
Set doc = db.CreateDocument
On Error GoTo 0
doc.Form = "Memo"
doc.importance = "1" '(Where 1=Urgent, 2= Normal, 3= FYI)
'Send an e-mail to
doc.sendTO = "email address goes here"
doc.RETURNRECEIPT = "1"
doc.Subject = "Test Email"
Set rtItem = doc.CreateRichTextItem("Body")
Call rtItem.AppendText("Test Email")
Call rtItem.AddNewLine(2)
Call rtItem.AppendText("Dear sir or madam")
Call rtItem.AddNewLine(1)
Call rtItem.AppendText("This is a test email for the Treasury
Department. If you have received this in error, please

...

read more »- Hide quoted text -

- Show quoted text -

 

[Erick C]

Hi Gina -
I am so sorry that I keep messing this thing up. I know you are tired
of sending me responses. Believe me, I am tired of looking at this
thing too. This will be my last access database for quite a while!
I get an error when I enter the name of the field after Me. So if I
put in Me.AcctNum I get an error saying Method or data member not
found. If I enter Me.[AcctNum] I get an error #2465 saying Microsoft
Access can't find the field "I" referred to in your expression.

Erick,

Sorry Me.NameOfAccNumField on your form.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Hi Gina -
I have changed the Call line, but I received an error saying "Complie
Error: Method or Data Member Not Found " and the debugger highlighted
the ".AcctNum" part of Me.AcctNum.

As far as the email portion goes, I will do my best to try and get it
going.  I have run into a snag already with the  'Retrieves pdf file
locations from an Access Database>     Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")

What goes into the To and Email Portion?
If I try to run it errors and highlights the above line, the error
says Error 424, Object Required

Erick,

Then adjust the line to say...

Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test\" &
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter, "000") &
".pdf",
False, False)

I added the dash so the code can gleam the AccNum which we can tell it
before the dash...

Now, I have some code to loops thru Lotus Notes and send a PDF report,
only
as I don't have Lotus Notes I have no way to test or even play to
modify...

'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()

Dim user As String, server As String, mailfile As String
Dim doc As Object, rtobject As Object
Dim session As Object, db As Object
Dim item As Object, rtitem As Variant
Dim Picture As Object

'Arrays to hold a list of Email Recipients and a list of
'pdf files to be attached to the email
Dim strSendTo() As String
Dim strPDFList() As String
Dim strBodyText As String
Dim strSubject As String
Dim i As Integer

'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")

'Adds all recipients to be emailed to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strSendTo(i)
strSendTo(i) = mrstAccess![To]
mrstAccess.MoveNext
i = i + 1
Loop

strSubject = txtSubject.Text
strBodyText = txtBody.Text

'Creates a Notessession Object and provides mail details
Set session = CreateObject("notes.notessession")
user = session.UserName
server = session.GETENVIRONMENTSTRING("MailServer", True)
mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
Set db = session.GETDATABASE(server, mailfile)
Set doc = db.CREATEDOCUMENT()
doc.Form = "Memo"
doc.sendto = strSendTo()
doc.Subject = strSubject
Set rtitem = doc.CreateRichTextItem("body")
Call rtitem.APPENDTEXT(strBodyText)
Call rtitem.ADDNEWLINE(2)

'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM PDFs")

'Attaches all pdf files specified to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strPDFList(i)
strPDFList(i) = mrstAccess![PDF_Name]
Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
mrstAccess.MoveNext
i = i + 1
Loop

'Send the mail
Call doc.Send(False)
Set doc = Nothing

End Sub
'***End of Code

There is also more information
here..http://www.bigresource.com/VB-Visual-Basic-to-send-Lotus-Notes-Email-...

Do you think you can modify the above?

"I feel I have been denied critical, need to know, information!" - Tremors
II

"Erick C" <[email protected]> wrote in message

Absolutely, Customer ID (AcctNum) can be sued to identify the reports.

Erick,
Okay... now the we need to find a way to identify the report for each
Customer... I suggested using the CustomerID is that an option for you?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
Sorry, I had an error in my code. I fixed it and the code now creates
individual pdf files for each customer. The file is named
"Sweeps_Report_For_Email000.pdf" and "Sweeps_Report_For_Email001.pdf"
Good morning Gina!
AcctNum is a text field.
Erick,
Is it looking at a text field or a numeric field?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
I am not the biggest fan of Lotus Notes myself, this would be so
much
easier if I was using Outlook. Regarding your email code question,
yes you are correct. The code as it is written emails only one
address at a time. I had guessed that it would be better and easier
to integrate into another code. I also added a list box to my form
that pulls in the customer ID (AcctNum), customer name, and email
address.
I thought that the same list box could be used to pull an email
address right after it was used to create and name the pdf. So, the
identifying portion of the name of the file would come from the
customer ID column in the list box, then the email address from the
email section of the list box. SO basically right after a pdf is
made
it is emailed and then it moves on to the next customer ID in the
list
box. Does my weird thought process make any sense at all, or was I
way off?
OK, I have changed the code attached to the button, but I have
received an error saying Compile error: can't find project or
library. The debugger highlights Format at the beginning of "Format
(intCounter,"000")"
Here is what my code looks like, did I add something that I was not
supposed to?
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![CustomerListBox]
For intCounter = 0 To CustomerListBox.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test
\Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False,
False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
Erick,
First, the report has to drop out to individual files, one way or
the
other... So whether you use a combo box or a list box doesn't
matter.
But
it must loop thru and attach a number or something on to the end
of
each
file so it knows the reports are seperate entities. Might be
better
to
pick
to pick the CustomerID as the report name so when eMailing the
eMail
knows
which report to grab. (I can modify to include CustomerID.)
'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![YourComboBox]
For intCounter = 0 To Combo0.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[YourFieldControlSource] = '" & cboCode.ItemData(intCounter)&
"'"
DoEvents
Call ConvertReportToPDF("Report1", ,
"DriveLetter\FolderName\Sweeps_Report_For_Email" &
Format(intCounter,
"000")
& ".pdf", False, False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
The code you supplied below appears to go to one eMail address at
a
time...
Is this true? I don't have Lotus Notes so no way to test.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
The email addresses are not actually part of a combo box on my
form,
I
just have them all in a Customers_To_Email table. The field is
"Email
Address" in the table.
I need to somehow set up a loop with the code so an individual
report
is created for each customer in the table. The test PDF that was
created has all customers in it, which would not be too cool at
all.
I also have a working code for emailing out of Lotus Notes, though
I
do not know how to integrate it into the PDF code, but maybe it
can
help.
Dim s As Object
Dim db As Object
Dim doc As Object
Dim rtItem As Object
Dim Server As String, Database As String
Dim strError As String
'startup Lotus notes and get object handle
Set s = CreateObject("Notes.notesSession")
Server = s.GETENVIRONMENTSTRING("MailServer", True)
Database = s.GETENVIRONMENTSTRING("MailFile", True)
Set db = s.GetDatabase(Server, Database)
On Error GoTo ErrorLogon
'see if user is logged on
Set doc = db.CreateDocument
On Error GoTo 0
doc.Form = "Memo"
doc.importance = "1" '(Where 1=Urgent, 2= Normal, 3= FYI)
'Send an e-mail to
doc.sendTO = "email address goes here"
doc.RETURNRECEIPT = "1"
doc.Subject = "Test Email"
Set rtItem = doc.CreateRichTextItem("Body")
Call rtItem.AppendText("Test Email")
Call rtItem.AddNewLine(2)
Call rtItem.AppendText("Dear sir or madam")
Call rtItem.AddNewLine(1)
Call rtItem.AppendText("This is a test email for the Treasury
Department. If you have received this in error, please

...

read more »- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Erick C]

Oh, and if I put Me.[Combo24]![AcctNum] I get a type mismatch error.

Hi Gina -
I am so sorry that I keep messing this thing up.  I know you are tired
of sending me responses.  Believe me, I am tired of looking at this
thing too.  This will be my last access database for quite a while!
I get an error when I enter the name of the field after Me.  So if I
put in Me.AcctNum I get an error saying Method or data member not
found.  If I enter Me.[AcctNum] I get an error #2465 saying Microsoft
Access can't find the field "I" referred to in your expression.

Erick,

Sorry Me.NameOfAccNumField on your form.

"I feel I have been denied critical, need to know, information!" - Tremors
II

"Erick C" <[email protected]> wrote in message

Hi Gina -
I have changed the Call line, but I received an error saying "Complie
Error: Method or Data Member Not Found " and the debugger highlighted
the ".AcctNum" part of Me.AcctNum.

As far as the email portion goes, I will do my best to try and get it
going.  I have run into a snag already with the  'Retrieves pdf file
locations from an Access Database>     Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")

What goes into the To and Email Portion?
If I try to run it errors and highlights the above line, the error
says Error 424, Object Required

Erick,
Then adjust the line to say...
Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test\"&
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter, "000") &
".pdf",
False, False)
I added the dash so the code can gleam the AccNum which we can tell it
before the dash...
Now, I have some code to loops thru Lotus Notes and send a PDF report,
only
as I don't have Lotus Notes I have no way to test or even play to
modify...
'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()
Dim user As String, server As String, mailfile As String
Dim doc As Object, rtobject As Object
Dim session As Object, db As Object
Dim item As Object, rtitem As Variant
Dim Picture As Object
'Arrays to hold a list of Email Recipients and a list of
'pdf files to be attached to the email
Dim strSendTo() As String
Dim strPDFList() As String
Dim strBodyText As String
Dim strSubject As String
Dim i As Integer
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")
'Adds all recipients to be emailed to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strSendTo(i)
strSendTo(i) = mrstAccess![To]
mrstAccess.MoveNext
i = i + 1
Loop
strSubject = txtSubject.Text
strBodyText = txtBody.Text
'Creates a Notessession Object and provides mail details
Set session = CreateObject("notes.notessession")
user = session.UserName
server = session.GETENVIRONMENTSTRING("MailServer", True)
mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
Set db = session.GETDATABASE(server, mailfile)
Set doc = db.CREATEDOCUMENT()
doc.Form = "Memo"
doc.sendto = strSendTo()
doc.Subject = strSubject
Set rtitem = doc.CreateRichTextItem("body")
Call rtitem.APPENDTEXT(strBodyText)
Call rtitem.ADDNEWLINE(2)
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM PDFs")
'Attaches all pdf files specified to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strPDFList(i)
strPDFList(i) = mrstAccess![PDF_Name]
Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
mrstAccess.MoveNext
i = i + 1
Loop
'Send the mail
Call doc.Send(False)
Set doc = Nothing
End Sub
'***End of Code
There is also more information
here..http://www.bigresource.com/VB-Visual-Basic-to-send-Lotus-Notes-Email-...
Do you think you can modify the above?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Absolutely, Customer ID (AcctNum) can be sued to identify the reports..
Erick,
Okay... now the we need to find a way to identify the report for each
Customer... I suggested using the CustomerID is that an option for you?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
Sorry, I had an error in my code. I fixed it and the code now creates
individual pdf files for each customer. The file is named
"Sweeps_Report_For_Email000.pdf" and "Sweeps_Report_For_Email001.pdf"
Good morning Gina!
AcctNum is a text field.
Erick,
Is it looking at a text field or a numeric field?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
I am not the biggest fan of Lotus Notes myself, this would be so
much
easier if I was using Outlook. Regarding your email code question,
yes you are correct. The code as it is written emails only one
address at a time. I had guessed that it would be better and easier
to integrate into another code. I also added a list box to my form
that pulls in the customer ID (AcctNum), customer name, and email
address.
I thought that the same list box could be used to pull an email
address right after it was used to create and name the pdf. So,the
identifying portion of the name of the file would come from the
customer ID column in the list box, then the email address fromthe
email section of the list box. SO basically right after a pdf is
made
it is emailed and then it moves on to the next customer ID in the
list
box. Does my weird thought process make any sense at all, or was I
way off?
OK, I have changed the code attached to the button, but I have
received an error saying Compile error: can't find project or
library. The debugger highlights Format at the beginning of "Format
(intCounter,"000")"
Here is what my code looks like, did I add something that I wasnot
supposed to?
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![CustomerListBox]
For intCounter = 0 To CustomerListBox.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test
\Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False,
False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
Erick,
First, the report has to drop out to individual files, one way or
the
other... So whether you use a combo box or a list box doesn't
matter.
But
it must loop thru and attach a number or something on to the end
of
each
file so it knows the reports are seperate entities. Might be
better
to
pick
to pick the CustomerID as the report name so when eMailing the
eMail
knows
which report to grab. (I can modify to include CustomerID.)
'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![YourComboBox]
For intCounter = 0 To Combo0.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[YourFieldControlSource] = '" & cboCode.ItemData(intCounter) &
"'"
DoEvents
Call ConvertReportToPDF("Report1", ,
"DriveLetter\FolderName\Sweeps_Report_For_Email" &
Format(intCounter,
"000")
& ".pdf", False, False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
The code you supplied below appears to go to one eMail address at
a
time...
Is this true? I don't have Lotus Notes so no way to test.
--

...

read more »- Hide quoted text -

- Show quoted text -

 

[Gina Whipp]

Erick,

No worries... we will get it... what is the name of the field that has
AccNum cause it sounds like you should put Me.Combo24

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Oh, and if I put Me.[Combo24]![AcctNum] I get a type mismatch error.

Hi Gina -
I am so sorry that I keep messing this thing up. I know you are tired
of sending me responses. Believe me, I am tired of looking at this
thing too. This will be my last access database for quite a while!
I get an error when I enter the name of the field after Me. So if I
put in Me.AcctNum I get an error saying Method or data member not
found. If I enter Me.[AcctNum] I get an error #2465 saying Microsoft
Access can't find the field "I" referred to in your expression.

Erick,

Sorry Me.NameOfAccNumField on your form.

"I feel I have been denied critical, need to know, information!" -
Tremors
II

"Erick C" <[email protected]> wrote in message

Hi Gina -
I have changed the Call line, but I received an error saying "Complie
Error: Method or Data Member Not Found " and the debugger highlighted
the ".AcctNum" part of Me.AcctNum.

As far as the email portion goes, I will do my best to try and get it
going. I have run into a snag already with the 'Retrieves pdf file
locations from an Access Database> Set mrstAccess =
mconAccess.Execute("SELECT To FROM Email")

What goes into the To and Email Portion?
If I try to run it errors and highlights the above line, the error
says Error 424, Object Required

Erick,
Then adjust the line to say...
Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test\"
&
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter, "000") &
".pdf",
False, False)
I added the dash so the code can gleam the AccNum which we can tell it
before the dash...
Now, I have some code to loops thru Lotus Notes and send a PDF report,
only
as I don't have Lotus Notes I have no way to test or even play to
modify...
'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()
Dim user As String, server As String, mailfile As String
Dim doc As Object, rtobject As Object
Dim session As Object, db As Object
Dim item As Object, rtitem As Variant
Dim Picture As Object
'Arrays to hold a list of Email Recipients and a list of
'pdf files to be attached to the email
Dim strSendTo() As String
Dim strPDFList() As String
Dim strBodyText As String
Dim strSubject As String
Dim i As Integer
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")
'Adds all recipients to be emailed to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strSendTo(i)
strSendTo(i) = mrstAccess![To]
mrstAccess.MoveNext
i = i + 1
Loop
strSubject = txtSubject.Text
strBodyText = txtBody.Text
'Creates a Notessession Object and provides mail details
Set session = CreateObject("notes.notessession")
user = session.UserName
server = session.GETENVIRONMENTSTRING("MailServer", True)
mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
Set db = session.GETDATABASE(server, mailfile)
Set doc = db.CREATEDOCUMENT()
doc.Form = "Memo"
doc.sendto = strSendTo()
doc.Subject = strSubject
Set rtitem = doc.CreateRichTextItem("body")
Call rtitem.APPENDTEXT(strBodyText)
Call rtitem.ADDNEWLINE(2)
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM PDFs")
'Attaches all pdf files specified to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strPDFList(i)
strPDFList(i) = mrstAccess![PDF_Name]
Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
mrstAccess.MoveNext
i = i + 1
Loop
'Send the mail
Call doc.Send(False)
Set doc = Nothing
End Sub
'***End of Code
There is also more information
here..http://www.bigresource.com/VB-Visual-Basic-to-send-Lotus-Notes-Email-...
Do you think you can modify the above?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Absolutely, Customer ID (AcctNum) can be sued to identify the reports.
Erick,
Okay... now the we need to find a way to identify the report for
each
Customer... I suggested using the CustomerID is that an option for
you?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
Sorry, I had an error in my code. I fixed it and the code now
creates
individual pdf files for each customer. The file is named
"Sweeps_Report_For_Email000.pdf" and
"Sweeps_Report_For_Email001.pdf"
Good morning Gina!
AcctNum is a text field.
On Feb 3, 8:46 am, "Gina Whipp" <[email protected]>
wrote:
Erick,
Is it looking at a text field or a numeric field?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
I am not the biggest fan of Lotus Notes myself, this would be so
much
easier if I was using Outlook. Regarding your email code
question,
yes you are correct. The code as it is written emails only one
address at a time. I had guessed that it would be better and
easier
to integrate into another code. I also added a list box to my
form
that pulls in the customer ID (AcctNum), customer name, and
email
address.
I thought that the same list box could be used to pull an email
address right after it was used to create and name the pdf. So,
the
identifying portion of the name of the file would come from the
customer ID column in the list box, then the email address from
the
email section of the list box. SO basically right after a pdf is
made
it is emailed and then it moves on to the next customer ID in
the
list
box. Does my weird thought process make any sense at all, or was
I
way off?
OK, I have changed the code attached to the button, but I have
received an error saying Compile error: can't find project or
library. The debugger highlights Format at the beginning of
"Format
(intCounter,"000")"
Here is what my code looks like, did I add something that I was
not
supposed to?
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![CustomerListBox]
For intCounter = 0 To CustomerListBox.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Report1", ,
"C:\Users\Erick\Documents\Test
\Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False,
False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
On Feb 2, 8:37 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
First, the report has to drop out to individual files, one way
or
the
other... So whether you use a combo box or a list box doesn't
matter.
But
it must loop thru and attach a number or something on to the
end
of
each
file so it knows the reports are seperate entities. Might be
better
to
pick
to pick the CustomerID as the report name so when eMailing the
eMail
knows
which report to grab. (I can modify to include CustomerID.)
'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![YourComboBox]
For intCounter = 0 To Combo0.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[YourFieldControlSource] = '" & cboCode.ItemData(intCounter)
&
"'"
DoEvents
Call ConvertReportToPDF("Report1", ,
"DriveLetter\FolderName\Sweeps_Report_For_Email" &
Format(intCounter,
"000")
& ".pdf", False, False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
The code you supplied below appears to go to one eMail address
at
a
time...
Is this true? I don't have Lotus Notes so no way to test.
--

...

read more »- Hide quoted text -

- Show quoted text -

 

[Erick C]

I also tried Me.Combo24, but it only gives a name if I actually select
something in the combo box. So if I make no selection at all I get
nothing in the name. And If I make a selection in the combo box then
every pdf that is created comes up with the same name.

Erick,

No worries... we will get it... what is the name of the field that has
AccNum cause it sounds like you should put Me.Combo24

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Oh, and if I put Me.[Combo24]![AcctNum] I get a type mismatch error.

Hi Gina -
I am so sorry that I keep messing this thing up. I know you are tired
of sending me responses. Believe me, I am tired of looking at this
thing too. This will be my last access database for quite a while!
I get an error when I enter the name of the field after Me. So if I
put in Me.AcctNum I get an error saying Method or data member not
found. If I enter Me.[AcctNum] I get an error #2465 saying Microsoft
Access can't find the field "I" referred to in your expression.

Erick,
Sorry Me.NameOfAccNumField on your form.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
I have changed the Call line, but I received an error saying "Complie
Error: Method or Data Member Not Found " and the debugger highlighted
the ".AcctNum" part of Me.AcctNum.
As far as the email portion goes, I will do my best to try and get it
going. I have run into a snag already with the 'Retrieves pdf file
locations from an Access Database> Set mrstAccess =
mconAccess.Execute("SELECT To FROM Email")
What goes into the To and Email Portion?
If I try to run it errors and highlights the above line, the error
says Error 424, Object Required
Erick,
Then adjust the line to say...
Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test\"
&
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter, "000") &
".pdf",
False, False)
I added the dash so the code can gleam the AccNum which we can tellit
before the dash...
Now, I have some code to loops thru Lotus Notes and send a PDF report,
only
as I don't have Lotus Notes I have no way to test or even play to
modify...
'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()
Dim user As String, server As String, mailfile As String
Dim doc As Object, rtobject As Object
Dim session As Object, db As Object
Dim item As Object, rtitem As Variant
Dim Picture As Object
'Arrays to hold a list of Email Recipients and a list of
'pdf files to be attached to the email
Dim strSendTo() As String
Dim strPDFList() As String
Dim strBodyText As String
Dim strSubject As String
Dim i As Integer
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")
'Adds all recipients to be emailed to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strSendTo(i)
strSendTo(i) = mrstAccess![To]
mrstAccess.MoveNext
i = i + 1
Loop
strSubject = txtSubject.Text
strBodyText = txtBody.Text
'Creates a Notessession Object and provides mail details
Set session = CreateObject("notes.notessession")
user = session.UserName
server = session.GETENVIRONMENTSTRING("MailServer", True)
mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
Set db = session.GETDATABASE(server, mailfile)
Set doc = db.CREATEDOCUMENT()
doc.Form = "Memo"
doc.sendto = strSendTo()
doc.Subject = strSubject
Set rtitem = doc.CreateRichTextItem("body")
Call rtitem.APPENDTEXT(strBodyText)
Call rtitem.ADDNEWLINE(2)
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM PDFs")
'Attaches all pdf files specified to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strPDFList(i)
strPDFList(i) = mrstAccess![PDF_Name]
Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
mrstAccess.MoveNext
i = i + 1
Loop
'Send the mail
Call doc.Send(False)
Set doc = Nothing
End Sub
'***End of Code
There is also more information
here..http://www.bigresource.com/VB-Visual-Basic-to-send-Lotus-Notes-Email-...
Do you think you can modify the above?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Absolutely, Customer ID (AcctNum) can be sued to identify the reports.
Erick,
Okay... now the we need to find a way to identify the report for
each
Customer... I suggested using the CustomerID is that an option for
you?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
Sorry, I had an error in my code. I fixed it and the code now
creates
individual pdf files for each customer. The file is named
"Sweeps_Report_For_Email000.pdf" and
"Sweeps_Report_For_Email001.pdf"
Good morning Gina!
AcctNum is a text field.
On Feb 3, 8:46 am, "Gina Whipp" <[email protected]>
wrote:
Erick,
Is it looking at a text field or a numeric field?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
I am not the biggest fan of Lotus Notes myself, this would beso
much
easier if I was using Outlook. Regarding your email code
question,
yes you are correct. The code as it is written emails only one
address at a time. I had guessed that it would be better and
easier
to integrate into another code. I also added a list box to my
form
that pulls in the customer ID (AcctNum), customer name, and
email
address.
I thought that the same list box could be used to pull an email
address right after it was used to create and name the pdf. So,
the
identifying portion of the name of the file would come from the
customer ID column in the list box, then the email address from
the
email section of the list box. SO basically right after a pdfis
made
it is emailed and then it moves on to the next customer ID in
the
list
box. Does my weird thought process make any sense at all, or was
I
way off?
OK, I have changed the code attached to the button, but I have
received an error saying Compile error: can't find project or
library. The debugger highlights Format at the beginning of
"Format
(intCounter,"000")"
Here is what my code looks like, did I add something that I was
not
supposed to?
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![CustomerListBox]
For intCounter = 0 To CustomerListBox.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Report1", ,
"C:\Users\Erick\Documents\Test
\Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False,
False)
DoCmd.Close acReport, "Sweeps_Report_For_Email"
Next
On Feb 2, 8:37 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
First, the report has to drop out to individual files, one way
or
the
other... So whether you use a combo box or a list box doesn't
matter.
But
it must loop thru and attach a number or something on to the
end
of
each
file so it knows the reports are seperate entities. Might be
better
to

...

read more »- Hide quoted text -

- Show quoted text -

 

[Erick C]

Or I should clarify, I do not get anything before the -
Sweep_To_Email.pdf. So where we would expect to see the AcctNum,
there is nothing if nothing is selected in the combo box. But the pdf
still gets created, just missing the beginning part of the name.

I also tried Me.Combo24, but it only gives a name if I actually select
something in the combo box.  So if I make no selection at all I get
nothing in the name.  And If I make a selection in the combo box then
every pdf that is created comes up with the same name.

Erick,

No worries... we will get it... what is the name of the field that has
AccNum cause it sounds like you should put Me.Combo24

"I feel I have been denied critical, need to know, information!" - Tremors
II

"Erick C" <[email protected]> wrote in message

Oh, and if I put Me.[Combo24]![AcctNum] I get a type mismatch error.

Hi Gina -
I am so sorry that I keep messing this thing up. I know you are tired
of sending me responses. Believe me, I am tired of looking at this
thing too. This will be my last access database for quite a while!
I get an error when I enter the name of the field after Me. So if I
put in Me.AcctNum I get an error saying Method or data member not
found. If I enter Me.[AcctNum] I get an error #2465 saying Microsoft
Access can't find the field "I" referred to in your expression.
Erick,
Sorry Me.NameOfAccNumField on your form.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
I have changed the Call line, but I received an error saying "Complie
Error: Method or Data Member Not Found " and the debugger highlighted
the ".AcctNum" part of Me.AcctNum.
As far as the email portion goes, I will do my best to try and get it
going. I have run into a snag already with the 'Retrieves pdf file
locations from an Access Database> Set mrstAccess =
mconAccess.Execute("SELECT To FROM Email")
What goes into the To and Email Portion?
If I try to run it errors and highlights the above line, the error
says Error 424, Object Required
Erick,
Then adjust the line to say...
Call ConvertReportToPDF("Report1", , "C:\Users\Erick\Documents\Test\"
&
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter, "000") &
".pdf",
False, False)
I added the dash so the code can gleam the AccNum which we can tell it
before the dash...
Now, I have some code to loops thru Lotus Notes and send a PDF report,
only
as I don't have Lotus Notes I have no way to test or even play to
modify...
'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()
Dim user As String, server As String, mailfile As String
Dim doc As Object, rtobject As Object
Dim session As Object, db As Object
Dim item As Object, rtitem As Variant
Dim Picture As Object
'Arrays to hold a list of Email Recipients and a list of
'pdf files to be attached to the email
Dim strSendTo() As String
Dim strPDFList() As String
Dim strBodyText As String
Dim strSubject As String
Dim i As Integer
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")
'Adds all recipients to be emailed to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strSendTo(i)
strSendTo(i) = mrstAccess![To]
mrstAccess.MoveNext
i = i + 1
Loop
strSubject = txtSubject.Text
strBodyText = txtBody.Text
'Creates a Notessession Object and provides mail details
Set session = CreateObject("notes.notessession")
user = session.UserName
server = session.GETENVIRONMENTSTRING("MailServer", True)
mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
Set db = session.GETDATABASE(server, mailfile)
Set doc = db.CREATEDOCUMENT()
doc.Form = "Memo"
doc.sendto = strSendTo()
doc.Subject = strSubject
Set rtitem = doc.CreateRichTextItem("body")
Call rtitem.APPENDTEXT(strBodyText)
Call rtitem.ADDNEWLINE(2)
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM PDFs")
'Attaches all pdf files specified to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strPDFList(i)
strPDFList(i) = mrstAccess![PDF_Name]
Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
mrstAccess.MoveNext
i = i + 1
Loop
'Send the mail
Call doc.Send(False)
Set doc = Nothing
End Sub
'***End of Code
There is also more information
here..http://www.bigresource.com/VB-Visual-Basic-to-send-Lotus-Notes-Email-...
Do you think you can modify the above?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Absolutely, Customer ID (AcctNum) can be sued to identify the reports.
Erick,
Okay... now the we need to find a way to identify the report for
each
Customer... I suggested using the CustomerID is that an option for
you?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
Sorry, I had an error in my code. I fixed it and the code now
creates
individual pdf files for each customer. The file is named
"Sweeps_Report_For_Email000.pdf" and
"Sweeps_Report_For_Email001.pdf"
Good morning Gina!
AcctNum is a text field.
On Feb 3, 8:46 am, "Gina Whipp" <[email protected]>
wrote:
Erick,
Is it looking at a text field or a numeric field?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
I am not the biggest fan of Lotus Notes myself, this would be so
much
easier if I was using Outlook. Regarding your email code
question,
yes you are correct. The code as it is written emails only one
address at a time. I had guessed that it would be better and
easier
to integrate into another code. I also added a list box to my
form
that pulls in the customer ID (AcctNum), customer name, and
email
address.
I thought that the same list box could be used to pull an email
address right after it was used to create and name the pdf.So,
the
identifying portion of the name of the file would come fromthe
customer ID column in the list box, then the email address from
the
email section of the list box. SO basically right after a pdf is
made
it is emailed and then it moves on to the next customer ID in
the
list
box. Does my weird thought process make any sense at all, or was
I
way off?
OK, I have changed the code attached to the button, but I have
received an error saying Compile error: can't find project or
library. The debugger highlights Format at the beginning of
"Format
(intCounter,"000")"
Here is what my code looks like, did I add something that Iwas
not
supposed to?
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![CustomerListBox]
For intCounter = 0 To CustomerListBox.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Report1", ,
"C:\Users\Erick\Documents\Test
\Sweeps_Report_For_Email" & Format(intCounter,

...

read more »- Hide quoted text -

- Show quoted text -

 

[Gina Whipp]

Erick,

AccNum-Sweep_To_Email001.pdf

Please copy/paste the code you are using here... maybe I missed something!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Or I should clarify, I do not get anything before the -
Sweep_To_Email.pdf. So where we would expect to see the AcctNum,
there is nothing if nothing is selected in the combo box. But the pdf
still gets created, just missing the beginning part of the name.

I also tried Me.Combo24, but it only gives a name if I actually select
something in the combo box. So if I make no selection at all I get
nothing in the name. And If I make a selection in the combo box then
every pdf that is created comes up with the same name.

Erick,

No worries... we will get it... what is the name of the field that has
AccNum cause it sounds like you should put Me.Combo24

"I feel I have been denied critical, need to know, information!" -
Tremors
II

"Erick C" <[email protected]> wrote in message

Oh, and if I put Me.[Combo24]![AcctNum] I get a type mismatch error.

Hi Gina -
I am so sorry that I keep messing this thing up. I know you are tired
of sending me responses. Believe me, I am tired of looking at this
thing too. This will be my last access database for quite a while!
I get an error when I enter the name of the field after Me. So if I
put in Me.AcctNum I get an error saying Method or data member not
found. If I enter Me.[AcctNum] I get an error #2465 saying Microsoft
Access can't find the field "I" referred to in your expression.
Erick,
Sorry Me.NameOfAccNumField on your form.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
I have changed the Call line, but I received an error saying
"Complie
Error: Method or Data Member Not Found " and the debugger
highlighted
the ".AcctNum" part of Me.AcctNum.
As far as the email portion goes, I will do my best to try and get
it
going. I have run into a snag already with the 'Retrieves pdf file
locations from an Access Database> Set mrstAccess =
mconAccess.Execute("SELECT To FROM Email")
What goes into the To and Email Portion?
If I try to run it errors and highlights the above line, the error
says Error 424, Object Required
Erick,
Then adjust the line to say...
Call ConvertReportToPDF("Report1", ,
"C:\Users\Erick\Documents\Test\"
&
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter, "000")
&
".pdf",
False, False)
I added the dash so the code can gleam the AccNum which we can
tell it
before the dash...
Now, I have some code to loops thru Lotus Notes and send a PDF
report,
only
as I don't have Lotus Notes I have no way to test or even play to
modify...
'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()
Dim user As String, server As String, mailfile As String
Dim doc As Object, rtobject As Object
Dim session As Object, db As Object
Dim item As Object, rtitem As Variant
Dim Picture As Object
'Arrays to hold a list of Email Recipients and a list of
'pdf files to be attached to the email
Dim strSendTo() As String
Dim strPDFList() As String
Dim strBodyText As String
Dim strSubject As String
Dim i As Integer
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")
'Adds all recipients to be emailed to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strSendTo(i)
strSendTo(i) = mrstAccess![To]
mrstAccess.MoveNext
i = i + 1
Loop
strSubject = txtSubject.Text
strBodyText = txtBody.Text
'Creates a Notessession Object and provides mail details
Set session = CreateObject("notes.notessession")
user = session.UserName
server = session.GETENVIRONMENTSTRING("MailServer", True)
mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
Set db = session.GETDATABASE(server, mailfile)
Set doc = db.CREATEDOCUMENT()
doc.Form = "Memo"
doc.sendto = strSendTo()
doc.Subject = strSubject
Set rtitem = doc.CreateRichTextItem("body")
Call rtitem.APPENDTEXT(strBodyText)
Call rtitem.ADDNEWLINE(2)
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM PDFs")
'Attaches all pdf files specified to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strPDFList(i)
strPDFList(i) = mrstAccess![PDF_Name]
Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
mrstAccess.MoveNext
i = i + 1
Loop
'Send the mail
Call doc.Send(False)
Set doc = Nothing
End Sub
'***End of Code
There is also more information
here..http://www.bigresource.com/VB-Visual-Basic-to-send-Lotus-Notes-Email-...
Do you think you can modify the above?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Absolutely, Customer ID (AcctNum) can be sued to identify the
reports.
On Feb 3, 12:06 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Okay... now the we need to find a way to identify the report for
each
Customer... I suggested using the CustomerID is that an option
for
you?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
Sorry, I had an error in my code. I fixed it and the code now
creates
individual pdf files for each customer. The file is named
"Sweeps_Report_For_Email000.pdf" and
"Sweeps_Report_For_Email001.pdf"
Good morning Gina!
AcctNum is a text field.
On Feb 3, 8:46 am, "Gina Whipp" <[email protected]>
wrote:
Erick,
Is it looking at a text field or a numeric field?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
I am not the biggest fan of Lotus Notes myself, this would
be so
much
easier if I was using Outlook. Regarding your email code
question,
yes you are correct. The code as it is written emails only
one
address at a time. I had guessed that it would be better and
easier
to integrate into another code. I also added a list box to
my
form
that pulls in the customer ID (AcctNum), customer name, and
email
address.
I thought that the same list box could be used to pull an
email
address right after it was used to create and name the pdf.
So,
the
identifying portion of the name of the file would come from
the
customer ID column in the list box, then the email address
from
the
email section of the list box. SO basically right after a
pdf is
made
it is emailed and then it moves on to the next customer ID
in
the
list
box. Does my weird thought process make any sense at all, or
was
I
way off?
OK, I have changed the code attached to the button, but I
have
received an error saying Compile error: can't find project
or
library. The debugger highlights Format at the beginning of
"Format
(intCounter,"000")"
Here is what my code looks like, did I add something that I
was
not
supposed to?
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![CustomerListBox]
For intCounter = 0 To CustomerListBox.ListCount - 1
DoCmd.OpenReport "Sweeps_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Report1", ,
"C:\Users\Erick\Documents\Test
\Sweeps_Report_For_Email" & Format(intCounter,

...

read more »- Hide quoted text -

- Show quoted text -

 

[Erick C]

Hi Gina -
Here is the code that I currently have. This code exports files with
names like "-Sweep_To_Email000.pdf"

Dim intCounter As Integer
Dim cboCode As ComboBox


Set cboCode = Me![Combo24]


For intCounter = 0 To Combo24.ListCount - 1
DoCmd.OpenReport "Sweep_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Sweep_Report_For_Email", , "C:\Documents
and Settings\ecrump\My Documents\Access Test\Test Files\" & Me.Combo24
& "-Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False, False)
DoCmd.Close acReport, "Sweep_Report_For_Email"
Next

Erick,

AccNum-Sweep_To_Email001.pdf

Please copy/paste the code you are using here... maybe I missed something!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Or I should clarify, I do not get anything before the -
Sweep_To_Email.pdf.  So where we would expect to see the AcctNum,
there is nothing if nothing is selected in the combo box.  But the pdf
still gets created, just missing the beginning part of the name.

I also tried Me.Combo24, but it only gives a name if I actually select
something in the combo box. So if I make no selection at all I get
nothing in the name. And If I make a selection in the combo box then
every pdf that is created comes up with the same name.

Erick,
No worries... we will get it... what is the name of the field that has
AccNum cause it sounds like you should put Me.Combo24
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Oh, and if I put Me.[Combo24]![AcctNum] I get a type mismatch error.
Hi Gina -
I am so sorry that I keep messing this thing up. I know you are tired
of sending me responses. Believe me, I am tired of looking at this
thing too. This will be my last access database for quite a while!
I get an error when I enter the name of the field after Me. So if I
put in Me.AcctNum I get an error saying Method or data member not
found. If I enter Me.[AcctNum] I get an error #2465 saying Microsoft
Access can't find the field "I" referred to in your expression.
Erick,
Sorry Me.NameOfAccNumField on your form.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
I have changed the Call line, but I received an error saying
"Complie
Error: Method or Data Member Not Found " and the debugger
highlighted
the ".AcctNum" part of Me.AcctNum.
As far as the email portion goes, I will do my best to try and get
it
going. I have run into a snag already with the 'Retrieves pdf file
locations from an Access Database> Set mrstAccess =
mconAccess.Execute("SELECT To FROM Email")
What goes into the To and Email Portion?
If I try to run it errors and highlights the above line, the error
says Error 424, Object Required
Erick,
Then adjust the line to say...
Call ConvertReportToPDF("Report1", ,
"C:\Users\Erick\Documents\Test\"
&
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter, "000")
&
".pdf",
False, False)
I added the dash so the code can gleam the AccNum which we can
tell it
before the dash...
Now, I have some code to loops thru Lotus Notes and send a PDF
report,
only
as I don't have Lotus Notes I have no way to test or even play to
modify...
'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()
Dim user As String, server As String, mailfile As String
Dim doc As Object, rtobject As Object
Dim session As Object, db As Object
Dim item As Object, rtitem As Variant
Dim Picture As Object
'Arrays to hold a list of Email Recipients and a list of
'pdf files to be attached to the email
Dim strSendTo() As String
Dim strPDFList() As String
Dim strBodyText As String
Dim strSubject As String
Dim i As Integer
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")
'Adds all recipients to be emailed to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strSendTo(i)
strSendTo(i) = mrstAccess![To]
mrstAccess.MoveNext
i = i + 1
Loop
strSubject = txtSubject.Text
strBodyText = txtBody.Text
'Creates a Notessession Object and provides mail details
Set session = CreateObject("notes.notessession")
user = session.UserName
server = session.GETENVIRONMENTSTRING("MailServer", True)
mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
Set db = session.GETDATABASE(server, mailfile)
Set doc = db.CREATEDOCUMENT()
doc.Form = "Memo"
doc.sendto = strSendTo()
doc.Subject = strSubject
Set rtitem = doc.CreateRichTextItem("body")
Call rtitem.APPENDTEXT(strBodyText)
Call rtitem.ADDNEWLINE(2)
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM PDFs")
'Attaches all pdf files specified to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strPDFList(i)
strPDFList(i) = mrstAccess![PDF_Name]
Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
mrstAccess.MoveNext
i = i + 1
Loop
'Send the mail
Call doc.Send(False)
Set doc = Nothing
End Sub
'***End of Code
There is also more information
here..http://www.bigresource.com/VB-Visual-Basic-to-send-Lotus-Notes-Email-...
Do you think you can modify the above?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Absolutely, Customer ID (AcctNum) can be sued to identify the
reports.
On Feb 3, 12:06 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Okay... now the we need to find a way to identify the report for
each
Customer... I suggested using the CustomerID is that an option
for
you?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
Sorry, I had an error in my code. I fixed it and the code now
creates
individual pdf files for each customer. The file is named
"Sweeps_Report_For_Email000.pdf" and
"Sweeps_Report_For_Email001.pdf"
Good morning Gina!
AcctNum is a text field.
On Feb 3, 8:46 am, "Gina Whipp" <[email protected]>
wrote:
Erick,
Is it looking at a text field or a numeric field?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
I am not the biggest fan of Lotus Notes myself, this would
be so
much
easier if I was using Outlook. Regarding your email code
question,
yes you are correct. The code as it is written emails only
one
address at a time. I had guessed that it would be better and
easier
to integrate into another code. I also added a list box to
my
form
that pulls in the customer ID (AcctNum), customer name, and
email
address.
I thought that the same list box could be used to pull an
email
address right after it was used to create and name the pdf.
So,
the
identifying portion of the name of the file would come from
the
customer

...

read more »- Hide quoted text -

- Show quoted text -

 

[Gina Whipp]

Erick,

My fault, it's not cycling the combo box to get CustomerID. Let me think on
this...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi Gina -
Here is the code that I currently have. This code exports files with
names like "-Sweep_To_Email000.pdf"

Dim intCounter As Integer
Dim cboCode As ComboBox


Set cboCode = Me![Combo24]


For intCounter = 0 To Combo24.ListCount - 1
DoCmd.OpenReport "Sweep_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Sweep_Report_For_Email", , "C:\Documents
and Settings\ecrump\My Documents\Access Test\Test Files\" & Me.Combo24
& "-Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False, False)
DoCmd.Close acReport, "Sweep_Report_For_Email"
Next

Erick,

AccNum-Sweep_To_Email001.pdf

Please copy/paste the code you are using here... maybe I missed something!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Or I should clarify, I do not get anything before the -
Sweep_To_Email.pdf. So where we would expect to see the AcctNum,
there is nothing if nothing is selected in the combo box. But the pdf
still gets created, just missing the beginning part of the name.

I also tried Me.Combo24, but it only gives a name if I actually select
something in the combo box. So if I make no selection at all I get
nothing in the name. And If I make a selection in the combo box then
every pdf that is created comes up with the same name.

Erick,
No worries... we will get it... what is the name of the field that has
AccNum cause it sounds like you should put Me.Combo24
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Oh, and if I put Me.[Combo24]![AcctNum] I get a type mismatch error.
Hi Gina -
I am so sorry that I keep messing this thing up. I know you are
tired
of sending me responses. Believe me, I am tired of looking at this
thing too. This will be my last access database for quite a while!
I get an error when I enter the name of the field after Me. So if I
put in Me.AcctNum I get an error saying Method or data member not
found. If I enter Me.[AcctNum] I get an error #2465 saying Microsoft
Access can't find the field "I" referred to in your expression.
Erick,
Sorry Me.NameOfAccNumField on your form.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
I have changed the Call line, but I received an error saying
"Complie
Error: Method or Data Member Not Found " and the debugger
highlighted
the ".AcctNum" part of Me.AcctNum.
As far as the email portion goes, I will do my best to try and get
it
going. I have run into a snag already with the 'Retrieves pdf file
locations from an Access Database> Set mrstAccess =
mconAccess.Execute("SELECT To FROM Email")
What goes into the To and Email Portion?
If I try to run it errors and highlights the above line, the error
says Error 424, Object Required
On Feb 3, 1:37 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Then adjust the line to say...
Call ConvertReportToPDF("Report1", ,
"C:\Users\Erick\Documents\Test\"
&
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter,
"000")
&
".pdf",
False, False)
I added the dash so the code can gleam the AccNum which we can
tell it
before the dash...
Now, I have some code to loops thru Lotus Notes and send a PDF
report,
only
as I don't have Lotus Notes I have no way to test or even play
to
modify...
'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()
Dim user As String, server As String, mailfile As String
Dim doc As Object, rtobject As Object
Dim session As Object, db As Object
Dim item As Object, rtitem As Variant
Dim Picture As Object
'Arrays to hold a list of Email Recipients and a list of
'pdf files to be attached to the email
Dim strSendTo() As String
Dim strPDFList() As String
Dim strBodyText As String
Dim strSubject As String
Dim i As Integer
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")
'Adds all recipients to be emailed to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strSendTo(i)
strSendTo(i) = mrstAccess![To]
mrstAccess.MoveNext
i = i + 1
Loop
strSubject = txtSubject.Text
strBodyText = txtBody.Text
'Creates a Notessession Object and provides mail details
Set session = CreateObject("notes.notessession")
user = session.UserName
server = session.GETENVIRONMENTSTRING("MailServer", True)
mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
Set db = session.GETDATABASE(server, mailfile)
Set doc = db.CREATEDOCUMENT()
doc.Form = "Memo"
doc.sendto = strSendTo()
doc.Subject = strSubject
Set rtitem = doc.CreateRichTextItem("body")
Call rtitem.APPENDTEXT(strBodyText)
Call rtitem.ADDNEWLINE(2)
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM PDFs")
'Attaches all pdf files specified to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strPDFList(i)
strPDFList(i) = mrstAccess![PDF_Name]
Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
mrstAccess.MoveNext
i = i + 1
Loop
'Send the mail
Call doc.Send(False)
Set doc = Nothing
End Sub
'***End of Code
There is also more information
here..http://www.bigresource.com/VB-Visual-Basic-to-send-Lotus-Notes-Email-...
Do you think you can modify the above?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Absolutely, Customer ID (AcctNum) can be sued to identify the
reports.
On Feb 3, 12:06 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Okay... now the we need to find a way to identify the report
for
each
Customer... I suggested using the CustomerID is that an option
for
you?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
Sorry, I had an error in my code. I fixed it and the code now
creates
individual pdf files for each customer. The file is named
"Sweeps_Report_For_Email000.pdf" and
"Sweeps_Report_For_Email001.pdf"
Good morning Gina!
AcctNum is a text field.
On Feb 3, 8:46 am, "Gina Whipp"
<[email protected]>
wrote:
Erick,
Is it looking at a text field or a numeric field?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
I am not the biggest fan of Lotus Notes myself, this would
be so
much
easier if I was using Outlook. Regarding your email code
question,
yes you are correct. The code as it is written emails only
one
address at a time. I had guessed that it would be better
and
easier
to integrate into another code. I also added a list box to
my
form
that pulls in the customer ID (AcctNum), customer name,
and
email
address.
I thought that the same list box could be used to pull an
email
address right after it was used to create and name the
pdf.
So,
the
identifying portion of the name of the file would come
from
the
customer

...

read more »- Hide quoted text -

- Show quoted text -

 

[Erick C]

Gina -
No problem. I am just happy that I was not going crazy, or I had
entered something incorrectly.

Erick,

My fault, it's not cycling the combo box to get CustomerID.  Let me think on
this...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Hi Gina -
Here is the code that I currently have.  This code exports files with
names like "-Sweep_To_Email000.pdf"

Dim intCounter As Integer
Dim cboCode As ComboBox

Set cboCode = Me![Combo24]

For intCounter = 0 To Combo24.ListCount - 1
  DoCmd.OpenReport "Sweep_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
  DoEvents
  Call ConvertReportToPDF("Sweep_Report_For_Email", , "C:\Documents
and Settings\ecrump\My Documents\Access Test\Test Files\" & Me.Combo24
& "-Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False, False)
  DoCmd.Close acReport, "Sweep_Report_For_Email"
Next

Erick,

Please copy/paste the code you are using here... maybe I missed something!

"I feel I have been denied critical, need to know, information!" - Tremors
II

"Erick C" <[email protected]> wrote in message

Or I should clarify, I do not get anything before the -
Sweep_To_Email.pdf. So where we would expect to see the AcctNum,
there is nothing if nothing is selected in the combo box. But the pdf
still gets created, just missing the beginning part of the name.

I also tried Me.Combo24, but it only gives a name if I actually select
something in the combo box. So if I make no selection at all I get
nothing in the name. And If I make a selection in the combo box then
every pdf that is created comes up with the same name.
Erick,
No worries... we will get it... what is the name of the field that has
AccNum cause it sounds like you should put Me.Combo24
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Oh, and if I put Me.[Combo24]![AcctNum] I get a type mismatch error..
Hi Gina -
I am so sorry that I keep messing this thing up. I know you are
tired
of sending me responses. Believe me, I am tired of looking at this
thing too. This will be my last access database for quite a while!
I get an error when I enter the name of the field after Me. So ifI
put in Me.AcctNum I get an error saying Method or data member not
found. If I enter Me.[AcctNum] I get an error #2465 saying Microsoft
Access can't find the field "I" referred to in your expression.
Erick,
Sorry Me.NameOfAccNumField on your form.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
I have changed the Call line, but I received an error saying
"Complie
Error: Method or Data Member Not Found " and the debugger
highlighted
the ".AcctNum" part of Me.AcctNum.
As far as the email portion goes, I will do my best to try and get
it
going. I have run into a snag already with the 'Retrieves pdf file
locations from an Access Database> Set mrstAccess =
mconAccess.Execute("SELECT To FROM Email")
What goes into the To and Email Portion?
If I try to run it errors and highlights the above line, the error
says Error 424, Object Required
On Feb 3, 1:37 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Then adjust the line to say...
Call ConvertReportToPDF("Report1", ,
"C:\Users\Erick\Documents\Test\"
&
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter,
"000")
&
".pdf",
False, False)
I added the dash so the code can gleam the AccNum which we can
tell it
before the dash...
Now, I have some code to loops thru Lotus Notes and send a PDF
report,
only
as I don't have Lotus Notes I have no way to test or even play
to
modify...
'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()
Dim user As String, server As String, mailfile As String
Dim doc As Object, rtobject As Object
Dim session As Object, db As Object
Dim item As Object, rtitem As Variant
Dim Picture As Object
'Arrays to hold a list of Email Recipients and a list of
'pdf files to be attached to the email
Dim strSendTo() As String
Dim strPDFList() As String
Dim strBodyText As String
Dim strSubject As String
Dim i As Integer
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")
'Adds all recipients to be emailed to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strSendTo(i)
strSendTo(i) = mrstAccess![To]
mrstAccess.MoveNext
i = i + 1
Loop
strSubject = txtSubject.Text
strBodyText = txtBody.Text
'Creates a Notessession Object and provides mail details
Set session = CreateObject("notes.notessession")
user = session.UserName
server = session.GETENVIRONMENTSTRING("MailServer", True)
mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
Set db = session.GETDATABASE(server, mailfile)
Set doc = db.CREATEDOCUMENT()
doc.Form = "Memo"
doc.sendto = strSendTo()
doc.Subject = strSubject
Set rtitem = doc.CreateRichTextItem("body")
Call rtitem.APPENDTEXT(strBodyText)
Call rtitem.ADDNEWLINE(2)
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM PDFs")
'Attaches all pdf files specified to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strPDFList(i)
strPDFList(i) = mrstAccess![PDF_Name]
Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
mrstAccess.MoveNext
i = i + 1
Loop
'Send the mail
Call doc.Send(False)
Set doc = Nothing
End Sub
'***End of Code
There is also more information
here..http://www.bigresource.com/VB-Visual-Basic-to-send-Lotus-Notes-Email-...
Do you think you can modify the above?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Absolutely, Customer ID (AcctNum) can be sued to identify the
reports.
On Feb 3, 12:06 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Okay... now the we need to find a way to identify the report
for
each
Customer... I suggested using the CustomerID is that an option
for
you?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
Sorry, I had an error in my code. I fixed it and the code now
creates
individual pdf files for each customer. The file is named
"Sweeps_Report_For_Email000.pdf" and
"Sweeps_Report_For_Email001.pdf"
Good morning Gina!
AcctNum is a text field.
On Feb 3, 8:46 am, "Gina Whipp"
<[email protected]>
wrote:

...

read more »- Hide quoted text -

- Show quoted text -

 

[Gina Whipp]

Erick,

I am going to have to admit this one has me stumped... Anything I do to
loop thru the data to extract the CustomerID still only grabs the first
CustomerID. I am trying to find a way to identify the report so you know
which eMail to attach it to without opening each report and checking it.
Not giving up just yet... just not sure to get it to do what I want it to
do...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina -
No problem. I am just happy that I was not going crazy, or I had
entered something incorrectly.

Erick,

My fault, it's not cycling the combo box to get CustomerID. Let me think
on
this...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Hi Gina -
Here is the code that I currently have. This code exports files with
names like "-Sweep_To_Email000.pdf"

Dim intCounter As Integer
Dim cboCode As ComboBox

Set cboCode = Me![Combo24]

For intCounter = 0 To Combo24.ListCount - 1
DoCmd.OpenReport "Sweep_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Sweep_Report_For_Email", , "C:\Documents
and Settings\ecrump\My Documents\Access Test\Test Files\" & Me.Combo24
& "-Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False, False)
DoCmd.Close acReport, "Sweep_Report_For_Email"
Next

Erick,

Please copy/paste the code you are using here... maybe I missed
something!

"I feel I have been denied critical, need to know, information!" -
Tremors
II

"Erick C" <[email protected]> wrote in message

Or I should clarify, I do not get anything before the -
Sweep_To_Email.pdf. So where we would expect to see the AcctNum,
there is nothing if nothing is selected in the combo box. But the pdf
still gets created, just missing the beginning part of the name.

I also tried Me.Combo24, but it only gives a name if I actually select
something in the combo box. So if I make no selection at all I get
nothing in the name. And If I make a selection in the combo box then
every pdf that is created comes up with the same name.
Erick,
No worries... we will get it... what is the name of the field that
has
AccNum cause it sounds like you should put Me.Combo24
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Oh, and if I put Me.[Combo24]![AcctNum] I get a type mismatch error.
Hi Gina -
I am so sorry that I keep messing this thing up. I know you are
tired
of sending me responses. Believe me, I am tired of looking at this
thing too. This will be my last access database for quite a while!
I get an error when I enter the name of the field after Me. So if
I
put in Me.AcctNum I get an error saying Method or data member not
found. If I enter Me.[AcctNum] I get an error #2465 saying
Microsoft
Access can't find the field "I" referred to in your expression.
On Feb 3, 2:50 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Sorry Me.NameOfAccNumField on your form.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
I have changed the Call line, but I received an error saying
"Complie
Error: Method or Data Member Not Found " and the debugger
highlighted
the ".AcctNum" part of Me.AcctNum.
As far as the email portion goes, I will do my best to try and
get
it
going. I have run into a snag already with the 'Retrieves pdf
file
locations from an Access Database> Set mrstAccess =
mconAccess.Execute("SELECT To FROM Email")
What goes into the To and Email Portion?
If I try to run it errors and highlights the above line, the
error
says Error 424, Object Required
On Feb 3, 1:37 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Then adjust the line to say...
Call ConvertReportToPDF("Report1", ,
"C:\Users\Erick\Documents\Test\"
&
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter,
"000")
&
".pdf",
False, False)
I added the dash so the code can gleam the AccNum which we can
tell it
before the dash...
Now, I have some code to loops thru Lotus Notes and send a PDF
report,
only
as I don't have Lotus Notes I have no way to test or even play
to
modify...
'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()
Dim user As String, server As String, mailfile As String
Dim doc As Object, rtobject As Object
Dim session As Object, db As Object
Dim item As Object, rtitem As Variant
Dim Picture As Object
'Arrays to hold a list of Email Recipients and a list of
'pdf files to be attached to the email
Dim strSendTo() As String
Dim strPDFList() As String
Dim strBodyText As String
Dim strSubject As String
Dim i As Integer
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")
'Adds all recipients to be emailed to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strSendTo(i)
strSendTo(i) = mrstAccess![To]
mrstAccess.MoveNext
i = i + 1
Loop
strSubject = txtSubject.Text
strBodyText = txtBody.Text
'Creates a Notessession Object and provides mail details
Set session = CreateObject("notes.notessession")
user = session.UserName
server = session.GETENVIRONMENTSTRING("MailServer", True)
mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
Set db = session.GETDATABASE(server, mailfile)
Set doc = db.CREATEDOCUMENT()
doc.Form = "Memo"
doc.sendto = strSendTo()
doc.Subject = strSubject
Set rtitem = doc.CreateRichTextItem("body")
Call rtitem.APPENDTEXT(strBodyText)
Call rtitem.ADDNEWLINE(2)
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM
PDFs")
'Attaches all pdf files specified to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strPDFList(i)
strPDFList(i) = mrstAccess![PDF_Name]
Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
mrstAccess.MoveNext
i = i + 1
Loop
'Send the mail
Call doc.Send(False)
Set doc = Nothing
End Sub
'***End of Code
There is also more information
here..http://www.bigresource.com/VB-Visual-Basic-to-send-Lotus-Notes-Email-...
Do you think you can modify the above?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Absolutely, Customer ID (AcctNum) can be sued to identify the
reports.
On Feb 3, 12:06 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Okay... now the we need to find a way to identify the report
for
each
Customer... I suggested using the CustomerID is that an
option
for
you?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
Sorry, I had an error in my code. I fixed it and the code
now
creates
individual pdf files for each customer. The file is named
"Sweeps_Report_For_Email000.pdf" and
"Sweeps_Report_For_Email001.pdf"
Good morning Gina!
AcctNum is a text field.
On Feb 3, 8:46 am, "Gina Whipp"
<[email protected]>
wrote:

...

read more »- Hide quoted text -

- Show quoted text -

 

[Erick C]

Hi Gina -
I am searching away on my side to see if I can find anything that may
help out. I will have to check out a few more websites once I get
home.
I do have a code that I use in another database, though the purpose is
different. Maybe some of the code might give you an idea.
The code creates dummy queries for each individual managerID and then
exports a xls file for each managerID. I cannot tell you everything
that the code is doing, but I know that it looks at a managerID table
in the database and adds the managerID field to the name of the
exported file and moves to the next one. Just thought it may possibly
help, or not.

Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic
names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value &
"'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & " " & strMgr &
".xls"
strPathFile = strPath & "\" & strFile
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, strTemp, strPathFile
rstMgr.MoveNext
Loop

Erick,

I am going to have to admit this one has me stumped...  Anything I do to
loop thru the data to extract the CustomerID still only grabs the first
CustomerID.  I am trying to find a way to identify the report so you know
which eMail to attach it to without opening each report and checking it.
Not giving up just yet...  just not sure to get it to do what I want itto
do...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Gina -
No problem.  I am just happy that I was not going crazy, or I had
entered something incorrectly.

Erick,

My fault, it's not cycling the combo box to get CustomerID. Let me think
on
this...

"I feel I have been denied critical, need to know, information!" - Tremors
II

"Erick C" <[email protected]> wrote in message

Hi Gina -
Here is the code that I currently have. This code exports files with
names like "-Sweep_To_Email000.pdf"

Dim intCounter As Integer
Dim cboCode As ComboBox

Set cboCode = Me![Combo24]

For intCounter = 0 To Combo24.ListCount - 1
DoCmd.OpenReport "Sweep_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Sweep_Report_For_Email", , "C:\Documents
and Settings\ecrump\My Documents\Access Test\Test Files\" & Me.Combo24
& "-Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False, False)
DoCmd.Close acReport, "Sweep_Report_For_Email"
Next

Erick,
AccNum-Sweep_To_Email001.pdf
Please copy/paste the code you are using here... maybe I missed
something!
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Or I should clarify, I do not get anything before the -
Sweep_To_Email.pdf. So where we would expect to see the AcctNum,
there is nothing if nothing is selected in the combo box. But the pdf
still gets created, just missing the beginning part of the name.
I also tried Me.Combo24, but it only gives a name if I actually select
something in the combo box. So if I make no selection at all I get
nothing in the name. And If I make a selection in the combo box then
every pdf that is created comes up with the same name.
Erick,
No worries... we will get it... what is the name of the field that
has
AccNum cause it sounds like you should put Me.Combo24
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Oh, and if I put Me.[Combo24]![AcctNum] I get a type mismatch error.
Hi Gina -
I am so sorry that I keep messing this thing up. I know you are
tired
of sending me responses. Believe me, I am tired of looking at this
thing too. This will be my last access database for quite a while!
I get an error when I enter the name of the field after Me. So if
I
put in Me.AcctNum I get an error saying Method or data member not
found. If I enter Me.[AcctNum] I get an error #2465 saying
Microsoft
Access can't find the field "I" referred to in your expression.
On Feb 3, 2:50 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Sorry Me.NameOfAccNumField on your form.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
I have changed the Call line, but I received an error saying
"Complie
Error: Method or Data Member Not Found " and the debugger
highlighted
the ".AcctNum" part of Me.AcctNum.
As far as the email portion goes, I will do my best to try and
get
it
going. I have run into a snag already with the 'Retrieves pdf
file
locations from an Access Database> Set mrstAccess =
mconAccess.Execute("SELECT To FROM Email")
What goes into the To and Email Portion?
If I try to run it errors and highlights the above line, the
error
says Error 424, Object Required
On Feb 3, 1:37 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Then adjust the line to say...
Call ConvertReportToPDF("Report1", ,
"C:\Users\Erick\Documents\Test\"
&
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter,
"000")
&
".pdf",
False, False)
I added the dash so the code can gleam the AccNum which we can
tell it
before the dash...
Now, I have some code to loops thru Lotus Notes and send a PDF
report,
only
as I don't have Lotus Notes I have no way to test or even play
to
modify...
'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()
Dim user As String, server As String, mailfile As String
Dim doc As Object, rtobject As Object
Dim session As Object, db As Object
Dim item As Object, rtitem As Variant
Dim Picture As Object
'Arrays to hold a list of Email Recipients and a list of
'pdf files to be attached to the email
Dim strSendTo() As String
Dim strPDFList() As String
Dim strBodyText As String
Dim strSubject As String
Dim i As Integer
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")
'Adds all recipients to be emailed to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strSendTo(i)
strSendTo(i) = mrstAccess![To]
mrstAccess.MoveNext
i = i + 1
Loop
strSubject = txtSubject.Text
strBodyText = txtBody.Text
'Creates a Notessession Object and provides mail details
Set session = CreateObject("notes.notessession")
user = session.UserName
server = session.GETENVIRONMENTSTRING("MailServer", True)
mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
Set db = session.GETDATABASE(server, mailfile)
Set doc = db.CREATEDOCUMENT()
doc.Form = "Memo"
doc.sendto = strSendTo()
doc.Subject = strSubject
Set rtitem = doc.CreateRichTextItem("body")
Call rtitem.APPENDTEXT(strBodyText)
Call rtitem.ADDNEWLINE(2)
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM
PDFs")
'Attaches all pdf files specified to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strPDFList(i)
strPDFList(i) = mrstAccess![PDF_Name]
Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
mrstAccess.MoveNext
i = i + 1
Loop
'Send the mail
Call doc.Send(False)
Set doc = Nothing
End Sub
'***End of Code
There is also more information
here..http://www.bigresource.com/VB-Visual-Basic-to-send-Lotus-Notes-Email-...
Do you think you can modify the above?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Absolutely, Customer ID (AcctNum) can be sued to identify the
reports.

...

read more »- Hide quoted text -

- Show quoted text -

 

[Gina Whipp]

Erick,

Thanks, it might give me an idea!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi Gina -
I am searching away on my side to see if I can find anything that may
help out. I will have to check out a few more websites once I get
home.
I do have a code that I use in another database, though the purpose is
different. Maybe some of the code might give you an idea.
The code creates dummy queries for each individual managerID and then
exports a xls file for each managerID. I cannot tell you everything
that the code is doing, but I know that it looks at a managerID table
in the database and adds the managerID field to the name of the
exported file and moves to the next one. Just thought it may possibly
help, or not.

Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic
names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value &
"'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & " " & strMgr &
".xls"
strPathFile = strPath & "\" & strFile
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, strTemp, strPathFile
rstMgr.MoveNext
Loop

Erick,

I am going to have to admit this one has me stumped... Anything I do to
loop thru the data to extract the CustomerID still only grabs the first
CustomerID. I am trying to find a way to identify the report so you know
which eMail to attach it to without opening each report and checking it.
Not giving up just yet... just not sure to get it to do what I want it to
do...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Gina -
No problem. I am just happy that I was not going crazy, or I had
entered something incorrectly.

Erick,

My fault, it's not cycling the combo box to get CustomerID. Let me think
on
this...

"I feel I have been denied critical, need to know, information!" -
Tremors
II

"Erick C" <[email protected]> wrote in message

Hi Gina -
Here is the code that I currently have. This code exports files with
names like "-Sweep_To_Email000.pdf"

Dim intCounter As Integer
Dim cboCode As ComboBox

Set cboCode = Me![Combo24]

For intCounter = 0 To Combo24.ListCount - 1
DoCmd.OpenReport "Sweep_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Sweep_Report_For_Email", , "C:\Documents
and Settings\ecrump\My Documents\Access Test\Test Files\" & Me.Combo24
& "-Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False, False)
DoCmd.Close acReport, "Sweep_Report_For_Email"
Next

Erick,
AccNum-Sweep_To_Email001.pdf
Please copy/paste the code you are using here... maybe I missed
something!
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Or I should clarify, I do not get anything before the -
Sweep_To_Email.pdf. So where we would expect to see the AcctNum,
there is nothing if nothing is selected in the combo box. But the pdf
still gets created, just missing the beginning part of the name.
I also tried Me.Combo24, but it only gives a name if I actually
select
something in the combo box. So if I make no selection at all I get
nothing in the name. And If I make a selection in the combo box then
every pdf that is created comes up with the same name.
Erick,
No worries... we will get it... what is the name of the field that
has
AccNum cause it sounds like you should put Me.Combo24
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Oh, and if I put Me.[Combo24]![AcctNum] I get a type mismatch
error.
Hi Gina -
I am so sorry that I keep messing this thing up. I know you are
tired
of sending me responses. Believe me, I am tired of looking at
this
thing too. This will be my last access database for quite a
while!
I get an error when I enter the name of the field after Me. So
if
I
put in Me.AcctNum I get an error saying Method or data member
not
found. If I enter Me.[AcctNum] I get an error #2465 saying
Microsoft
Access can't find the field "I" referred to in your expression.
On Feb 3, 2:50 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Sorry Me.NameOfAccNumField on your form.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
I have changed the Call line, but I received an error saying
"Complie
Error: Method or Data Member Not Found " and the debugger
highlighted
the ".AcctNum" part of Me.AcctNum.
As far as the email portion goes, I will do my best to try and
get
it
going. I have run into a snag already with the 'Retrieves pdf
file
locations from an Access Database> Set mrstAccess =
mconAccess.Execute("SELECT To FROM Email")
What goes into the To and Email Portion?
If I try to run it errors and highlights the above line, the
error
says Error 424, Object Required
On Feb 3, 1:37 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Then adjust the line to say...
Call ConvertReportToPDF("Report1", ,
"C:\Users\Erick\Documents\Test\"
&
Me.AccNum & "-Sweeps_Report_For_Email" & Format(intCounter,
"000")
&
".pdf",
False, False)
I added the dash so the code can gleam the AccNum which we
can
tell it
before the dash...
Now, I have some code to loops thru Lotus Notes and send a
PDF
report,
only
as I don't have Lotus Notes I have no way to test or even
play
to
modify...
'***Start of Code (looks to be attached to a command button)
Private Sub cmdSendMail_Click()
Dim user As String, server As String, mailfile As String
Dim doc As Object, rtobject As Object
Dim session As Object, db As Object
Dim item As Object, rtitem As Variant
Dim Picture As Object
'Arrays to hold a list of Email Recipients and a list of
'pdf files to be attached to the email
Dim strSendTo() As String
Dim strPDFList() As String
Dim strBodyText As String
Dim strSubject As String
Dim i As Integer
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT To FROM Email")
'Adds all recipients to be emailed to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strSendTo(i)
strSendTo(i) = mrstAccess![To]
mrstAccess.MoveNext
i = i + 1
Loop
strSubject = txtSubject.Text
strBodyText = txtBody.Text
'Creates a Notessession Object and provides mail details
Set session = CreateObject("notes.notessession")
user = session.UserName
server = session.GETENVIRONMENTSTRING("MailServer", True)
mailfile = session.GETENVIRONMENTSTRING("MailFile", True)
Set db = session.GETDATABASE(server, mailfile)
Set doc = db.CREATEDOCUMENT()
doc.Form = "Memo"
doc.sendto = strSendTo()
doc.Subject = strSubject
Set rtitem = doc.CreateRichTextItem("body")
Call rtitem.APPENDTEXT(strBodyText)
Call rtitem.ADDNEWLINE(2)
'Retrieves pdf file locations from an Access Database
Set mrstAccess = mconAccess.Execute("SELECT PDF_Name FROM
PDFs")
'Attaches all pdf files specified to the email
i = 1
Do While Not mrstAccess.EOF
ReDim Preserve strPDFList(i)
strPDFList(i) = mrstAccess![PDF_Name]
Set item = rtitem.EMBEDOBJECT(1454, "", strPDFList(i))
mrstAccess.MoveNext
i = i + 1
Loop
'Send the mail
Call doc.Send(False)
Set doc = Nothing
End Sub
'***End of Code
There is also more information
here..http://www.bigresource.com/VB-Visual-Basic-to-send-Lotus-Notes-Email-...
Do you think you can modify the above?
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Absolutely, Customer ID (AcctNum) can be sued to identify
the
reports.

...

read more »- Hide quoted text -

- Show quoted text -

 

[Erick C]

Hi Gina -
I have some more code for you. I found something that is referencing
the same Lebans ConvertReportToPDF. I thought it may help. From the
description I believe it is supposed to loop through different
CustomerIDs:


Private Sub cmdBulkConvertIndividualCustReports_Click()

'Notes:-------------------------------------------------------------------------------
'- You need to download this file from Lebans.com:
'(http://www.lebans.com/DownloadFiles/A2000SnapshotToPDFver785.zip
'... then copy the DLL's to your Windows/system32 folder,
'(Or to the same folder this database is in.)
'-------------------------------------------------------------------------------------

On Error GoTo Err_cmdEmailIndividualCustReports_Click

'Declare Variables and Objects
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strCustID As String
Dim strEmail As String
Dim strReportName As String
Dim blRet As Boolean

'Set Variables and Objects
strSQL = "SELECT CustomerID,EmailAddress From tblCustomers"
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)
strReportName = "rptEmailCustReportCustomers"

rst.MoveFirst

'Turns off the screen
DoCmd.Echo False
Do While Not rst.EOF
strCustID = rst!CustomerID
strEmail = rst!EmailAddress
'If CustomerID is a number change to: "CustomerID=" &
strCustID
DoCmd.OpenReport strReportName, acViewPreview, ,
"CustomerID=" & "'" & strCustID & "'"

'This is the call to the Stephen Lebans code.
blRet = ConvertReportToPDF(strReportName,
vbNullString, "C:\YourFolder\" & strReportName & "_Cust_" & strCustID
& ".pdf", False, False, 150, "", "", 0, 0, 0)

DoCmd.Close acReport, strReportName

rst.MoveNext
Loop
DoCmd.Echo True
MsgBox "Done", vbInformation

'Recordset Cleanup Code
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Exit_cmdEmailIndividualCustReports_Click:
'Avoids the endless loop
DoCmd.Echo True
On Error Resume Next
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Exit Sub

Err_cmdEmailIndividualCustReports_Click:

MsgBox "There was an error executing the command." _
& vbCrLf & vbCrLf & "Error " & Err.Number & ": " _
& vbCrLf & vbCrLf & Error, vbExclamation

Resume Exit_cmdEmailIndividualCustReports_Click

End Sub

Erick,

Thanks, it might give me an idea!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Hi Gina -
I am searching away on my side to see if I can find anything that may
help out.  I will have to check out a few more websites once I get
home.
I do have a code that I use in another database, though the purpose is
different.  Maybe some of the code might give you an idea.
The code creates dummy queries for each individual managerID and then
exports a xls file for each managerID.  I cannot tell you everything
that the code is doing, but I know that it looks at a managerID table
in the database and adds the managerID field to the name of the
exported file and moves to the next one.  Just thought it may possibly
help, or not.

Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
      MsgBox "No folder was selected.", vbOK, "No Selection"
      Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic
names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
      rstMgr.MoveFirst
      Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
           strMgr = DLookup("Subinventory", "Stores for Export", _
                  "Subinventory = '" & rstMgr!Subinventory.value &
"'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
            strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
                "Subinventory = '" & rstMgr!Subinventory.value & "';"
            Set qdf = dbs.QueryDefs(strTemp)
            qdf.Name = "q_" & strMgr
            strTemp = qdf.Name
            qdf.SQL = strSQL
            qdf.Close
            Set qdf = Nothing
          strFile = Format$(Date, "Medium Date") & " " & strMgr &
".xls"
          strPathFile = strPath & "\" & strFile
            DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, strTemp, strPathFile
          rstMgr.MoveNext
     Loop

Erick,

I am going to have to admit this one has me stumped... Anything I do to
loop thru the data to extract the CustomerID still only grabs the first
CustomerID. I am trying to find a way to identify the report so you know
which eMail to attach it to without opening each report and checking it..
Not giving up just yet... just not sure to get it to do what I want it to
do...

"I feel I have been denied critical, need to know, information!" - Tremors
II

"Erick C" <[email protected]> wrote in message

Gina -
No problem. I am just happy that I was not going crazy, or I had
entered something incorrectly.

Erick,
My fault, it's not cycling the combo box to get CustomerID. Let me think
on
this...
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
Here is the code that I currently have. This code exports files with
names like "-Sweep_To_Email000.pdf"
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![Combo24]
For intCounter = 0 To Combo24.ListCount - 1
DoCmd.OpenReport "Sweep_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Sweep_Report_For_Email", , "C:\Documents
and Settings\ecrump\My Documents\Access Test\Test Files\" & Me.Combo24
& "-Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False, False)
DoCmd.Close acReport, "Sweep_Report_For_Email"
Next
Erick,
AccNum-Sweep_To_Email001.pdf
Please copy/paste the code you are using here... maybe I missed
something!
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Or I should clarify, I do not get anything before the -
Sweep_To_Email.pdf. So where we would expect to see the AcctNum,
there is nothing if nothing is selected in the combo box. But the pdf
still gets created, just missing the beginning part of the name.
I also tried Me.Combo24, but it only gives a name if I actually
select
something in the combo box. So if I make no selection at all I get
nothing in the name. And If I make a selection in the combo box then
every pdf that is created comes up with the same name.
Erick,
No worries... we will get it... what is the name of the field that
has
AccNum cause it sounds like you should put Me.Combo24
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Oh, and if I put Me.[Combo24]![AcctNum] I get a type mismatch
error.
Hi Gina -
I am so sorry that I keep messing this thing up. I know you are
tired
of sending me responses. Believe me, I am tired of looking at
this
thing too. This will be my last access database for quite a
while!
I get an error when I enter the name of the field after Me. So
if
I
put in Me.AcctNum I get an error saying Method or data member
not
found. If I enter Me.[AcctNum] I get an error #2465 saying
Microsoft
Access can't find the field "I" referred to in your expression.
On Feb 3, 2:50 pm, "Gina Whipp" <[email protected]>
wrote:
Erick,
Sorry Me.NameOfAccNumField on your form.
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
I have changed the Call line, but I received an error saying
"Complie
Error: Method or Data Member Not Found " and the debugger
highlighted
the ".AcctNum" part of Me.AcctNum.
As far as the email portion goes, I will do my best to try and
get
it
going. I have run into a snag already with the 'Retrieves pdf
file
locations from an Access Database> Set mrstAccess =
mconAccess.Execute("SELECT To FROM Email")
What goes into the To and

...

read more »- Hide quoted text -

- Show quoted text -

 

[Erick C]

Gina -
I am happy to report that I have had a breakthrough!
I took the code that I sent you in the last post and messed around
with it for a while. I finally got the code to work. It creates an
individual PDF and adds the AcctNum to the name for each one.
Now I am going to start chipping away at the email part and see if I
can use some of the same stuff in this code for looping and finding
each customer's AcctNum and email address.
Thank you again for all of your patience and work. I know it was a
chore just getting me to this point.

Here is what I ended up using:

'Declare Variables and Objects
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strAcctNum As String
Dim strReportName As String


'Set Variables and Objects
strSQL = "SELECT AcctNum From Customers_To_Email"
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)
strReportName = "Sweep_Report_For_Email"


rst.MoveFirst


'Turns off the screen
DoCmd.Echo True
Do While Not rst.EOF
strAcctNum = rst!AcctNum
'If CustomerID is a number change to: "CustomerID="
&strCustID
DoCmd.OpenReport strReportName, acViewPreview, , "[BNY
Acct#]=" & "'" & strAcctNum & "'"


'This is the call to the Stephen Lebans code.
Call ConvertReportToPDF(strReportName, , "C:\Documents
and Settings\ecrump\My Documents\Access Test\Test Files\" & strAcctNum
& ".pdf", False, False)

DoCmd.Close acReport, strReportName

rst.MoveNext
Loop
DoCmd.Echo True
MsgBox "Done", vbInformation

'Recordset Cleanup Code
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Sub

Hi Gina -
I have some more code for you.  I found something that is referencing
the same Lebans ConvertReportToPDF.  I thought it may help.  From the
description I believe it is supposed to loop through different
CustomerIDs:

Private Sub cmdBulkConvertIndividualCustReports_Click()

'Notes:--------------------------------------------------------------------­-----------
'- You need to download this file from Lebans.com:
'(http://www.lebans.com/DownloadFiles/A2000SnapshotToPDFver785.zip
'... then copy the DLL's to your Windows/system32 folder,
'(Or to the same folder this database is in.)
'--------------------------------------------------------------------------­-----------

On Error GoTo Err_cmdEmailIndividualCustReports_Click

'Declare Variables and Objects
Dim dbs                 As DAO.Database
Dim rst                 As DAO.Recordset
Dim strSQL              As String
Dim strCustID           As String
Dim strEmail            As String
Dim strReportName       As String
Dim blRet               As Boolean

'Set Variables and Objects
strSQL = "SELECT CustomerID,EmailAddress From tblCustomers"
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)
strReportName = "rptEmailCustReportCustomers"

    rst.MoveFirst

    'Turns off the screen
    DoCmd.Echo False
        Do While Not rst.EOF
            strCustID = rst!CustomerID
            strEmail = rst!EmailAddress
            'If CustomerID is a number change to: "CustomerID=" &
strCustID
            DoCmd.OpenReport strReportName, acViewPreview, ,
"CustomerID=" & "'" & strCustID & "'"

                'This is the call to the Stephen Lebans code.
                blRet = ConvertReportToPDF(strReportName,
vbNullString, "C:\YourFolder\" & strReportName & "_Cust_" & strCustID
& ".pdf", False, False, 150, "", "", 0, 0, 0)

                DoCmd.Close acReport, strReportName

            rst.MoveNext
        Loop
    DoCmd.Echo True
    MsgBox "Done", vbInformation

'Recordset Cleanup Code
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Exit_cmdEmailIndividualCustReports_Click:
    'Avoids the endless loop
    DoCmd.Echo True
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    Exit Sub

Err_cmdEmailIndividualCustReports_Click:

    MsgBox "There was an error executing the command." _
    & vbCrLf & vbCrLf & "Error " & Err.Number & ": " _
    & vbCrLf & vbCrLf & Error, vbExclamation

    Resume Exit_cmdEmailIndividualCustReports_Click

End Sub

Erick,

Thanks, it might give me an idea!

"I feel I have been denied critical, need to know, information!" - Tremors
II

"Erick C" <[email protected]> wrote in message

Hi Gina -
I am searching away on my side to see if I can find anything that may
help out.  I will have to check out a few more websites once I get
home.
I do have a code that I use in another database, though the purpose is
different.  Maybe some of the code might give you an idea.
The code creates dummy queries for each individual managerID and then
exports a xls file for each managerID.  I cannot tell you everything
that the code is doing, but I know that it looks at a managerID table
in the database and adds the managerID field to the name of the
exported file and moves to the next one.  Just thought it may possibly
help, or not.

Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
      MsgBox "No folder was selected.", vbOK, "No Selection"
      Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic
names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
      rstMgr.MoveFirst
      Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
           strMgr = DLookup("Subinventory", "Stores for Export", _
                  "Subinventory = '" & rstMgr!Subinventory.value &
"'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
            strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
                "Subinventory = '" & rstMgr!Subinventory.value & "';"
            Set qdf = dbs.QueryDefs(strTemp)
            qdf.Name = "q_" & strMgr
            strTemp = qdf.Name
            qdf.SQL = strSQL
            qdf.Close
            Set qdf = Nothing
          strFile = Format$(Date, "Medium Date") & " " & strMgr &
".xls"
          strPathFile = strPath & "\" & strFile
            DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, strTemp, strPathFile
          rstMgr.MoveNext
     Loop

Erick,
I am going to have to admit this one has me stumped... Anything I do to
loop thru the data to extract the CustomerID still only grabs the first
CustomerID. I am trying to find a way to identify the report so you know
which eMail to attach it to without opening each report and checking it.
Not giving up just yet... just not sure to get it to do what I want it to
do...
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
No problem. I am just happy that I was not going crazy, or I had
entered something incorrectly.
Erick,
My fault, it's not cycling the combo box to get CustomerID. Let me think
on
this...
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
Here is the code that I currently have. This code exports files with
names like "-Sweep_To_Email000.pdf"
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![Combo24]
For intCounter = 0 To Combo24.ListCount - 1
DoCmd.OpenReport "Sweep_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Sweep_Report_For_Email", , "C:\Documents
and Settings\ecrump\My Documents\Access Test\Test Files\" & Me.Combo24
& "-Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False, False)
DoCmd.Close acReport, "Sweep_Report_For_Email"
Next
Erick,
AccNum-Sweep_To_Email001.pdf
Please copy/paste the code you are using here... maybe I missed
something!
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Or I should clarify, I do not get anything before the -
Sweep_To_Email.pdf. So where we would expect to see the AcctNum,
there is nothing if nothing is selected in the combo box. But thepdf
still gets created, just missing the beginning part of the name.
I also tried Me.Combo24, but it only gives a name

...

read more »- Hide quoted text -

- Show quoted text -