I do similar things: Our products are delivered with a Certificate of Analysis. I generate this one by opening an Excel template, pumping the required information from a query in Excel, saving it as pdf and attaching this pdf to a mail.
I think this will give you an idea about the possibilities with Access.
To generate and send CoA's took us before 2,5 days / week, a lot of manual handling and included several points where it could go wrong.
(and we know Murphy's law)
Now it is a press on a button + (if I programmed it correctly) free of errors
regards,
Ben
Example code
' Open Excel template to write
Set objExcelApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then Set objExcelApp = CreateObject("Excel.Application")
Set objworkbook = objExcelApp.Workbooks.Open(“YourFileName”)
'Write Date information to the opened Excel template
Row = 5
Col = "J"
objworkbook.Worksheets(Sheetnme).Range(Col & Row) = Date ‘etc
' you add your own lines here
objExcelApp.Application.Visible = True ' First make the created Excel visible for the user
With objworkbook
' Save now the file as new PDF
objworkbook.Save '(“YourNewFileName”)
.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Filenme _
, Quality:=xlQualityMinimum, IncludeDocProperties:=False, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End With
' Sending mail (We are using outlook 365, slow and it hangs from time to time but this is all I have)
Set oApp = CreateObject("outlook.application")
Set oMail = oApp.createitem(OLmailitem)
'28-aug
' Build the string for the body if the mail
Email_message = "Dear Customer, <br><br>Please find enclosed "
If Me.frm_NrOfCoA = 1 Then
Email_message = Email_message & "the CoA "
Else
Email_message = Email_message & Me.frm_NrOfCoA & " CoA's "
End If
Email_message = Email_message & "for our delivery " & Me.frm_Delivery & ". <br><br>With kind regards <br> <br>3B-Fibreglass Norway AS"
'SEND EMAIL (Creates mail, adds attachment and opens it such that the user can confirm and click on send”
Set oApp = CreateObject("outlook.application")
Set oMail = oApp.createitem(OLmailitem)
With oMail
.To = “Your SendTo” 'receiver
.CC = “Your SendCC” 'carbonCopy
.BCC = “Your SendBCC “
.Subject = “Your Subject” 'SubjectLine
.HTMLBody = “Your message”
' -------------------------------------
' Add the CoA(s) as attachment
' -------------------------------------
.Attachments.Add “Your FileName”
.Display ' show the mail to the user
' This part works only when OUTLOOK is opened
Do While .ReplyRecipients.Count > 0
.ReplyRecipients.Remove (1)
Loop
' .Recipients.Add (“Your reply_address”) 'reply_address
.ReplyRecipients.Add reply_address 'reply_address