add csv data save to already written out code on command button that creates a PDF out of data

[daniel]

Hello. I will post the code below. I have a form where supervisors input hourly figures from production lines into the access document. Once the info has been filled in there is a button that creates a PDF from inputted info, and emails it out to the directors and production manager. I want to add into the button code to create a csv file in a shared onedrive location, with the end goal having the csv sent to power BI where I will make tables and charts displaying the data.
How would I go about inputting this? I am fairly new to MS Access and have only fiddled around on the face level, so not done any coding yet, I am watching videos on youtube that are giving some indication but nothing that actually helps with what I am trying to add, I know it's all translatable but I'm not quite good enough at it yet to have the confidence in myself to input this.
Just as an FYI I didn't write this bit of code, my boss created this database a couple of years before I joined his team, so don't expect me to be an expert because the code looks written out properly.
Another FYI, The database is being used 24/5, so I don't particularly want to mess it up by trialling, we have backups that I can use but I believe the backup reads off of the original database, so if I break one, I break the other.
Option Compare Database



Private Sub Command22_Click()
Dim PauseTime, Start
PauseTime = 1# ' Set duration in seconds
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop

DoCmd.OutputTo acOutputReport, "Hourly_Report_2021_Email_New", acFormatPDF, "\\172.20.247.4\MFL Common Files\ACCESS FEEDBACK\Feedback 2021\hourly2021.pdf", False



Const cstrSMTPServer = "smtp.talktalkbusiness.net"

Const cintCDOSendUsingPort = 25

Const cintCDOSendUsing = 2

Dim objConfig, objMsg


Set objConfig = CreateObject("CDO.Configuration")


objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cintCDOSendUsing

objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = cintCDOSendUsingPort

objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = cstrSMTPServer

objConfig.Fields.Update


strSubject = "Hourly Efficiency Update"

strBody = "<P> Please find attached Hourly Efficiency Update. <P> Regards, <P>Feedback</P>"

strFile = "\\172.20.247.4\MFL Common Files\ACCESS FEEDBACK\Feedback 2021\hourly2021.pdf"

Set objMsg = CreateObject("CDO.Message")

Set objMsg.Configuration = objConfig



With objMsg

.From = "feedback@marchfoods.com"

.To = "fbhourly@marchfoods.com"

.Subject = strSubject

.HTMLBody = strBody

.AddAttachment strFile

.Send

Kill "\\172.20.247.4\MFL Common Files\ACCESS FEEDBACK\Feedback 2021\hourly2021.pdf"


End With

MsgBox "Report sent successfully. Click OK to continue"
End Sub

[Welshgasman]

Look at the TransferText command.

[davegri]

It appears that the data you want to send to the CSV is the same data sent to the PDF. That data is defined by the RecordSource property of the 'Hourly_Report_2021_Email_New' report. That would be what the TransferText command suggested by WGM above would need.

[daniel]

So essentially I just need to add a new line and state csv as the file type instead of PDF I take it?
will it automatically overwrite the previous version of the csv file?

[Welshgasman]

Look at the syntax https://learn.microsoft.com/en-us/of...d.transfertext
Yes, it will overwrite the file each time. If you do not want that, then add the date to the filename, so it is different each time. If multiple in a day, add the time element as well.