Macro to Import Information from Email Attachments into excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1178632%22%20slang%3D%22en-US%22%3EMacro%20to%20Import%20Information%20from%20Email%20Attachments%20into%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1178632%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20receive%20an%20email%20each%20day%20from%20a%20PDF%20and%20excel%20file%20which%20i%20then%20convert%20into%20another%20file.%20Essentially%20i%20remove%20some%20information%20and%20then%20issue%20as%20a%20CSV%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20this%20occurs%20daily%2C%20i%20am%20hoping%20to%20automate%20the%20process.%20I%20have%20done%20some%20research%20online%20and%20found%20some%20macros%20such%20as%20below.%20I%20am%20hoping%20someone%20can%20assist%20in%20updating%20to%20match%20my%20template%20so%20i%20import%20the%20correct%20attachment.%20In%20order%20to%20identify%20this%20i%20am%20hoping%20to%20use%20the%20emails%20%22Subject%20Header%22%20and%20name%20of%20each%20attachment%20to%20populate%20into%20excel%20as%20required.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20CopyAttachmentToExcel(olitem%20As%20Outlook.MailItem)%3CBR%20%2F%3EDim%20xlApp%20As%20Object%3CBR%20%2F%3EDim%20xlWB%20As%20Object%3CBR%20%2F%3EDim%20xlTempWB%20As%20Object%3CBR%20%2F%3EDim%20xlSheet%20As%20Object%3CBR%20%2F%3EDim%20xlTempSheet%20As%20Object%3CBR%20%2F%3EDim%20lngTempLast%20As%20Integer%3CBR%20%2F%3EDim%20lngLast%20As%20Integer%3CBR%20%2F%3EDim%20strFname%20As%20String%3CBR%20%2F%3EDim%20strTempPath%20As%20String%3CBR%20%2F%3EDim%20bXLStarted%20As%20Boolean%3C%2FP%3E%3CP%3EConst%20strPath%20As%20String%20%3D%20%22D%3A%5CMy%20Documents%5CCollate.xlsx%22%20'the%20path%20and%20name%20of%20the%20local%20workbook%3CBR%20%2F%3EstrTempPath%20%3D%20Left(strPath%2C%20InStrRev(strPath%2C%20%22%5C%22))%20'The%20path%20of%20the%20temporary%20file%3C%2FP%3E%3CP%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3ESet%20xlApp%20%3D%20GetObject(%2C%20%22Excel.Application%22)%3CBR%20%2F%3EIf%20Err%20%26lt%3B%26gt%3B%200%20Then%3CBR%20%2F%3ESet%20xlApp%20%3D%20CreateObject(%22Excel.Application%22)%3CBR%20%2F%3EbXLStarted%20%3D%20True%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ExlApp.Visible%20%3D%20True%3C%2FP%3E%3CP%3EOn%20Error%20GoTo%200%3CBR%20%2F%3E'Open%20the%20workbook%20to%20input%20the%20data%3CBR%20%2F%3ESet%20xlWB%20%3D%20xlApp.Workbooks.Open(strPath)%3CBR%20%2F%3ESet%20xlSheet%20%3D%20xlWB.Sheets(%22Sheet1%22)%20'The%20sheet%20in%20the%20local%20workbook%3C%2FP%3E%3CP%3E'Process%20the%20message%20attachment%3CBR%20%2F%3EWith%20olitem.Attachments.Item(1)%3CBR%20%2F%3EIf%20Right(.DisplayName%2C%204)%20%3D%20%22xlsx%22%20Then%3CBR%20%2F%3ElngLast%20%3D%20xlSheet.Range(%22A%22%20%26amp%3B%20xlSheet.Rows.Count).End(-4162).Row%3CBR%20%2F%3EstrFname%20%3D%20strTempPath%20%26amp%3B%20.DisplayName%3CBR%20%2F%3E.SaveAsFile%20strFname%3CBR%20%2F%3ESet%20xlTempWB%20%3D%20xlApp.Workbooks.Open(strFname%2C%20editable%3A%3DTrue)%3CBR%20%2F%3ESet%20xlTempSheet%20%3D%20xlTempWB.Sheets(%22DATA%22)%3CBR%20%2F%3ElngTempLast%20%3D%20xlTempSheet.Range(%22B%22%20%26amp%3B%20xlTempSheet.Rows.Count).End(-4162).Row%3CBR%20%2F%3ExlSheet.Range(%22A%22%20%26amp%3B%20lngLast%20%2B%201%2C%20%22S%22%20%26amp%3B%20lngLast%20%2B%20lngTempLast%20-%201).Value%20%3D%20xlTempSheet.Range(%22A2%22%2C%20%22S%22%20%26amp%3B%20lngTempLast).Value%3CBR%20%2F%3ExlWB.Save%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EEnd%20With%3CBR%20%2F%3ExlWB.Close%20SaveChanges%3A%3DTrue%3CBR%20%2F%3ExlTempWB.Close%20SaveChanges%3A%3DFalse%3CBR%20%2F%3EIf%20bXLStarted%20Then%3CBR%20%2F%3ExlApp.Quit%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ESet%20xlApp%20%3D%20Nothing%3CBR%20%2F%3ESet%20xlWB%20%3D%20Nothing%3CBR%20%2F%3ESet%20xlSheet%20%3D%20Nothing%3CBR%20%2F%3ESet%20xlTempWB%20%3D%20Nothing%3CBR%20%2F%3ESet%20xlTempSheet%20%3D%20Nothing%3CBR%20%2F%3ESet%20olitem%20%3D%20Nothing%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20please%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1178632%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Frequent Contributor

Hi,

 

I receive an email each day from a PDF and excel file which i then convert into another file. Essentially i remove some information and then issue as a CSV file.

 

As this occurs daily, i am hoping to automate the process. I have done some research online and found some macros such as below. I am hoping someone can assist in updating to match my template so i import the correct attachment. In order to identify this i am hoping to use the emails "Subject Header" and name of each attachment to populate into excel as required.

 

Sub CopyAttachmentToExcel(olitem As Outlook.MailItem)
Dim xlApp As Object
Dim xlWB As Object
Dim xlTempWB As Object
Dim xlSheet As Object
Dim xlTempSheet As Object
Dim lngTempLast As Integer
Dim lngLast As Integer
Dim strFname As String
Dim strTempPath As String
Dim bXLStarted As Boolean

Const strPath As String = "D:\My Documents\Collate.xlsx" 'the path and name of the local workbook
strTempPath = Left(strPath, InStrRev(strPath, "\")) 'The path of the temporary file

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Set xlApp = CreateObject("Excel.Application")
bXLStarted = True
End If
xlApp.Visible = True

On Error GoTo 0
'Open the workbook to input the data
Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets("Sheet1") 'The sheet in the local workbook

'Process the message attachment
With olitem.Attachments.Item(1)
If Right(.DisplayName, 4) = "xlsx" Then
lngLast = xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162).Row
strFname = strTempPath & .DisplayName
.SaveAsFile strFname
Set xlTempWB = xlApp.Workbooks.Open(strFname, editable:=True)
Set xlTempSheet = xlTempWB.Sheets("DATA")
lngTempLast = xlTempSheet.Range("B" & xlTempSheet.Rows.Count).End(-4162).Row
xlSheet.Range("A" & lngLast + 1, "S" & lngLast + lngTempLast - 1).Value = xlTempSheet.Range("A2", "S" & lngTempLast).Value
xlWB.Save
End If

End With
xlWB.Close SaveChanges:=True
xlTempWB.Close SaveChanges:=False
If bXLStarted Then
xlApp.Quit
End If
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
Set xlTempWB = Nothing
Set xlTempSheet = Nothing
Set olitem = Nothing
End Sub

 

Can someone please assist.

 

Many thanks,