CSV attachment to email not working when button pressed

RCheesley

RCheesley

Registered User.
Local time
Today, 13:03
Joined
Aug 12, 2008
Messages
243
Hi all,

On a database I inherited, there is a function whereby the data completed in the form can be exported to a CSV file which is automagically attached to an email ready to be sent.

Only problemo being when the button is pressed nothing happens :( If it's pressed and there's missing fields it does error to say fields need completing.

I'm not really sure where abouts to start looking - anyone got any suggestions?

Ruth
 
Can you post the code behind the button?
 
I *think* this is the code behind the button:

Private Sub Command73_Click()
On Error GoTo Err_Command73_Click

Dim l_result As String
Dim l_dir As String
Dim l_file_abv As String
Dim l_filename As String
Dim l_email_addr As String
Dim strEmail As String
Dim strMsg As String
Dim olook As Object
Dim oMail As Object
Dim rstSettings As ADODB.Recordset
Dim fldEach As ADODB.Field
Dim rstEmailaddr As ADODB.Recordset
Dim fldEach1 As ADODB.Field

Set rstSettings = New ADODB.Recordset
rstSettings.Open "SELECT dir & abv from settings", _
Application.CodeProject.Connection, _
adOpenStatic, adLockOptimistic, adCmdText
For Each fldEach In rstSettings.Fields
l_dir_value = fldEach.Value
Next
rstSettings.Close
Set rstSettings = Nothing

' get email addr

Set rstEmailaddr = New ADODB.Recordset
rstEmailaddr.Open "SELECT email_addr from settings", _
Application.CodeProject.Connection, _
adOpenStatic, adLockOptimistic, adCmdText
For Each fldEach1 In rstEmailaddr.Fields
l_email_addr = fldEach1.Value
Next
rstEmailaddr.Close
Set rstEmailaddr = Nothing

l_dir = l_dir_value & "1" ' Hospital abv with file type 1 = first file 2 = second file
l_filename = id & Replace(FormatDateTime(date, vbGeneralDate), "/", "") & ".CSV"

If IsNull(nhs_no) Or IsNull(organism_code) Or IsNull(patient_loc_id) Or IsNull(dob) Or IsNull(date_of_awareness) Or IsNull(sex) Or IsNull(age_of_admission) Or IsNull(date_of_admission) Or IsNull(pre_post_48_hours) Then
MsgBox "Cannot Save/Create file whilst record has no values.", vbCritical, "Proforma Creation Error"
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OutputTo acOutputQuery, "delimited_proforma_qry", acFormatTXT, l_dir & l_filename
created_on = date
MsgBox "Record Saved and File Successfully Created - " & l_dir & l_filename & " - EMAIL IS NOW BEING CREATED.", vbInformation, "File Creation Message"
' Email Settings
Set olook = CreateObject("Outlook.Application")
Set oMail = olook.createitem(0)
With oMail
.To = l_email_addr '"my@emailaddress.co.uk"
'.cc = "someone@anywhere.co.uk"
.body = "Attached is the RCA Stage1 Proforma File"
.Subject = "RCA Stage1 Profroma File"
.Attachments.Add l_dir & l_abv & l_filename
.Send
End With

Set oMail = Nothing
Set olook = Nothing
End If
Exit_Command73_Click:
Exit Sub
 
Hi Paul,

Thanks for taking the time to reply to me, I appreciate it.

Firstly, if the email had sent it should have popped into my inbox, and it hasnt. Nor is there anything in my outbox, so I assume it's not sent.

Secondly, it should pop up a warning asking for access to outlook, I don't think the creator added in the mod to supress this.

Not sure how to set a break point but will read the links you provided.

Ruth
 
Hopefully they'll help guide you through debugging. Do you get the message box that says "Record Saved and File Successfully Created..."?
 
Hi Paul,

A bit confusing seeing as I've never done anything with VBA before - I literally don't get ANYTHING .. it's almost like I am clicking a button with nothing associated with it.

No popups, nothing happens at all.

A bit weird as the first bit I mentioned where it gives me the error message is clearly working.
 
Can you post the db?
 
Can't do that Paul, it is confidential as is a fair amount of the data within. I can paste any code required though.
 
Understandable. If you set a breakpoint within that code and click the button, does it go to the breakpoint? What version of Access?
 
Not sure if I have done this right,I added a break point (red dot with a red line through the code), hit save, opened the form and filled out the required forms, clicked the button and nothing happened. Nada. No popups, no nothing :(

We use access 2003 here :)
 
Last edited:
Well, maybe code isn't running after all. With the form in design view, right click on the button to view its properties. On the events tab, it should say "Event Procedure" on the Click line. Does it? Once in a while the code gets disassociated from the control. If not, click on the ellipsis on that line (...) and it should take you to the code, and that should re-associate the code.
 
Ruth,

The picture and your description in the PM indicate you're using 2007. The reason I asked about version earlier is a potential problem with 2007, which will cause code not to run at all. Note the first item in the Configuration area (Trusted Locations):

http://allenbrowne.com/Access2007.html

Let's make sure that isn't the problem.
 
Paul,

Sorry if I didn't make it clear in the message.

We use 2003 at work, I don't have 2003 installed at home, only 2007. The error happens at work and at home, therefore I doubt it's due to 2007. Followed those steps to add to trusted location, still no joy.

Ruth
 
Last edited:
Paul,

An update, have spent some time getting at least used to the syntax of VBA, the code which is running on the button click is shown below.

I've been playing about with adding break points as you mentioned, and uncommenting out lines which I'm not sure why they were commented.

As an example, the line "DoCmd.OutputTo acOutputQuery, "delimited_proforma_qry", acFormatTXT, l_dir & l_filename" was commented out - when I removed the ' and put a breakpoint, I got the ominous eggtimer suggesting something was happening, but no error message or completion/email being sent.

I've checked that delimited_proforma_qry does actually exist.

The error I am getting now is "FILE CREATION UNSUCCESSFUL - Microsoft Office Access can't save the output data to the file you've selected" ... any ideas? The break is currently after :
.Subject = "RCA Stage1 Profroma File"
.Attachments.Add l_dir & l_abv & l_filename
.Send
End With

Final edit, hopefully, I figured out the problem - there was a typo in the directory where the files were trying to save! Hooray!!!!!

Code:

Private Sub save_but_Click()
On Error GoTo Err_save_but_Click

Dim l_result As String
Dim l_dir As String
Dim l_file_abv As String
Dim l_filename As String
Dim l_email_addr As String
Dim strEmail As String
Dim strMsg As String
Dim olook As Object
Dim oMail As Object
Dim rstSettings As ADODB.Recordset
Dim fldEach As ADODB.Field
Dim rstEmailaddr As ADODB.Recordset
Dim fldEach1 As ADODB.Field

DoCmd.SetWarnings False
Set rstSettings = New ADODB.Recordset
rstSettings.Open "SELECT dir & abv from settings", _
Application.CodeProject.Connection, _
adOpenStatic, adLockOptimistic, adCmdText
For Each fldEach In rstSettings.Fields
l_dir_value = fldEach.Value
Next
rstSettings.Close
Set rstSettings = Nothing

' get email addr

Set rstEmailaddr = New ADODB.Recordset
rstEmailaddr.Open "SELECT email_addr from settings", _
Application.CodeProject.Connection, _
adOpenStatic, adLockOptimistic, adCmdText
For Each fldEach1 In rstEmailaddr.Fields
l_email_addr = fldEach1.Value
Next
rstEmailaddr.Close
Set rstEmailaddr = Nothing

l_dir = l_dir_value & "1" ' Hospital abv with file type 1 = first file 2 = second file
'l_filename = id & nhs_no & Replace(FormatDateTime(specimen_date, vbGeneralDate), "/", "") & ".CSV"
l_filename = nhs_no & Replace(FormatDateTime(date_of_awareness, vbGeneralDate), "/", "") & ".CSV"
If IsNull(nhs_no) Or IsNull(organism_code) Or IsNull(patient_loc_id) Or IsNull(dob) Or IsNull(date_of_awareness) Or IsNull(sex) Or IsNull(age_of_admission) Or IsNull(date_of_admission) Or IsNull(pre_post_48_hours) Then
MsgBox "Cannot Save/Create file whilst record has no values.", vbCritical, "Proforma Creation Error"
Else
created_on = date
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OutputTo acOutputQuery, "delimited_proforma_qry", acFormatTXT, l_dir & l_filename
DoCmd.TransferText acExportDelim, "Stage 1 Export Specification", "delimited_proforma_qry", l_dir & l_filename
DoCmd.SetWarnings True
MsgBox "Record saved and file successfully created, email is being generated.", vbInformation, "File Creation Message"
' Email Settings
Set olook = CreateObject("Outlook.Application")
Set oMail = olook.createitem(0)
With oMail
.to = l_email_addr '"ruth.cheesley@suffolkpct.nhs.uk"
'.cc = "another@email"
.body = "Attached is the RCA Stage1 Proforma File"
.Subject = "RCA Stage1 Profroma File"
.Attachments.Add l_dir & l_abv & l_filename
.Send
End With

Set oMail = Nothing
Set olook = Nothing
prt_but.Enabled = True
email_but.Enabled = True
exit_but.SetFocus
save_but.Enabled = False
prt_but.SetFocus
nhs_no.Enabled = False
hospital_no.Enabled = False
organism_code.Enabled = False
'sex = False
dob.Enabled = False
diagnosis.Enabled = False
date_of_awareness.Enabled = False
date_of_admission.Enabled = False
repeat_case.Enabled = False
previous_admission.Enabled = False
gp_name.Enabled = False
'patient_loc_id.Enabled = False
patient_free_txt_loc.Enabled = False
augmented_care.Enabled = False
notes.Enabled = False
report_cause_of_death.Enabled = False
'part_death.Enabled = False
current_antib_txt.Enabled = False
previous_antib_txt.Enabled = False
mrsa_length_of_stay.Enabled = False
cdiff_length_of_stay.Enabled = False
current_antib_use.Enabled = False
previous_antib_use.Enabled = False
ppi_therapy.Enabled = False
surgery.Enabled = False
colonised.Enabled = False
immuno_suppressed.Enabled = False
indwelling_device.Enabled = False
no_surgical_wound.Enabled = False
End If
Exit_save_but_Click:
Exit Sub

Err_save_but_Click:
MsgBox "FILE CREATION UNSUCCESSFUL - " & Err.Description
Resume Exit_save_but_Click
End Sub
 
Last edited:
I like it when problems are solved while I sleep! Glad you got it sorted out.
 
You must log in or register to reply here.

Similar threads

N
Solved Export data from Passthrough Query to CSV
Replies
18
Views
1,669
Gasman
Gasman
A
Automation Error
Replies
10
Views
474
The_Doc_Man
The_Doc_Man
C
Help with looping through records
Replies
11
Views
577
cricketbird
C
T
Attache to email from attachment Field
Replies
2
Views
619
theinviter
T
N
Solved How to send multiple attachment with CDO Gmail in MS Access VBA
Replies
11
Views
1,498
nector
N

Users who are viewing this thread

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