VBA excel to attach created pdf to an access database

[ChrisPhillips]

Hi All,

Hope you can help me. I work in a call centre and run a training function that involves evaluating and coaching calls. I have set up an Excel form to convert a sheet in excel into a pdf and attach it to an email. It also adds the fields to a Database. However, I was hoping to add in a section that will also add the pdf as an attachment in the database along with the rest of the data.


I have already create the field in the database called pdf

Sub NewCall()
Dim password As String
Dim ws As Worksheet
Set ws = Sheet1
 'Copy from call monitoring to database
Application.ScreenUpdating = False
Dim db1 As Database
Dim rs As DAO.Recordset
Set db1 = OpenDatabase("Location")
Set rs = db1.OpenRecordset("Call_Data", dbOpenTable)
rs.AddNew
rs.Fields("") = Range("C2").Value
rs.Fields("") = Range("C4").Value
rs.Fields("") = Range("C3").Value
rs.Fields("") = Range("C11").Value
rs.Fields("") = Range("C5").Value
rs.Fields("") = Range("C6").Value
rs.Fields("") = Range("C7").Value
rs.Fields("") = Range("C10").Value
rs.Fields("") = Range("C9").Value
rs.Fields("") = Range("C8").Value
rs.Fields("") = Range("C12").Value
rs.Fields("") = Range("C13").Value
rs.Fields("") = Range("C14").Value
rs.Fields("") = Range("C15").Value
rs.Fields("") = Range("F20").Value
rs.Fields("") = Range("F26").Value
rs.Fields("") = Range("F32").Value
rs.Fields("") = Range("F38").Value
rs.Fields("") = Range("C16").Value
rs.Fields("") = Range("C17").Value
rs.Fields("") = Range("B43").Value
rs.Update
rs.Close,
db1.Close
Application.ScreenUpdating = True
 'Create PDF and Email Template
Application.ScreenUpdating = False
Sheet2.Visible = xlSheetVisible
Sheet2.Activate
Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object
 'Not sure for what the Title is
Title = Range("H3")
 'Define PDF Filename
PdfFile = ActiveWorkbook.FullName
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i-1)
PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"
 'Export active sheet as PDF
With ActiveSheet
 .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
 'Use already open Outlook if possible
On Error Resume Next
 Set OutlApp = GetObject(, "Outlook.Application")
 IsCreated = True
End If
OutlApp.Visible = True
OutlApp.Activate
On Error GoTo 0
 'Prepare Email with PDF Attachment
With OutlApp.CreateItem(0)
 'Prepare Email
.Subject = Title
.Body = "Hi, " & vbLf & vbLf _
 & "Please see attached a call that i marked including notes." & vbLf & vbLf
 & "Regards," & vbLf _
 & Application.Username & vbLf & vbLf
.Attachments.Add PdfFile
 'Try to send
On Error Resume Next
.Display
Active.Window = True
Application.Visible = True
End With
 'Delete PDF File
Kill PdfFile
 'Release the memory of object variable
Set OutlApp = Nothing
Sheet2.Visible = xlSheetHidden
 'Delete contents of cells
Sheet1.Activate
 Application.ScreenUpdating = False
 ActiveSheet.CheckBoxes.Value = False
 Range("C2,C5,C6,C7,E7,E6,E5,C8,C10,C11,C12,C13,C14,C15,C16,E8:E13,C17,C22:C29,D22:E29,B32:C41,D22:E29,D32:E41,B18:C19,D18:E19,B43:E48").Select
Range("B40").Activate
Selection.ClearContents
Range("C2").Select
Application.ScreenUpdating = True
End Sub

 

[alansidman]

crossposted: VBA to add information to Access Database but also add attachement - OzGrid Free Excel/VBA Help Forum

 

[Peter_SSs]

Please review our Forum Rules, especially in relation to cross-posting, and then update your thread with any other relevant links. Thanks.