Open attached CSV, copy newdata and paste it to database CSV

Not open for further replies.


New Member
Outlook version
Outlook 2010 64 bit
Email Account
Office 365 Exchange

I am receiving some scheduled emails with attached csv files. Currently I have a macro that saves the attached files to a specific folder, and then I manually open each csv, copy the new data and append it to the end of my database.csv file.

Is it possible to have an outlook macor that:
1- Save the attached imcoming.csv file.*
2- Open the incoming.csv
3- Copy the data from incoming.csv**
4- Open the database.csv
5- Append*** the copied range from incoming.csv to the end of database.csv
6- save files
6- Close csv files.

*I already have a code to download and save the attached part. The rest of the code is what I am missing.
**The incomin.csv file has headers and data row number is dynamic. I do not know how many row it has when arrived.
***the new data should be added to the end of my database.csv file. The database.csv keeps groving.

I would appreciate any help to achive this.
Thank you.

Thank you for the hint Diane. I checked different samples and managed to put together an outlook code that does what I wanted.

It does all that I listed in my first mail. Outlook processes the attached file and then deletes the incoming mail as well.

I trigger this macro with outlook rules when a mail arrives with the subject I defined in the rule.

Public Sub AddtoDatabase2() 
    Dim objOL As Outlook.Application 
    Dim pobjMsg As Outlook.MailItem 'Object 
    Dim objSelection As Outlook.Selection 
    ' Get the path to your My Documents folder 
    strFolderpath = CreateObject("WScript.Shell").SpecialFolders(16) 
    On Error Resume Next 
    ' Instantiate an Outlook Application object. 
    Set objOL = CreateObject("Outlook.Application") 
    ' Get the collection of selected objects. 
    Set objSelection = objOL.ActiveExplorer.Selection 
    For Each pobjMsg In objSelection 
            AddtoDatabase2_Parameter pobjMsg 
    Set pobjMsg = Nothing 
    Set objSelection = Nothing 
    Set objOL = Nothing 
End Sub 
Public Sub AddtoDatabase2_Parameter(objMsg As MailItem) 
    Dim objAttachments As Outlook.Attachments 
    Dim i As Long 
    Dim lngCount As Long 
    Dim strFile As String 
    Dim strFolderpath As String 
    Dim strDeletedFiles As String    
   ' Get the path to your My Documents folder 
    strFolderpath = CreateObject("WScript.Shell").SpecialFolders(16) 
    ' On Error Resume Next 
    ' Set the Attachment folder. 
    strFolderpath = "C:\test\" 
        ' Get the Attachments collection of the item. 
        Set objAttachments = objMsg.Attachments 
        lngCount = objAttachments.Count 
        If lngCount > 0 Then 
            ' We need to use a count down loop for removing items 
            ' from a collection. Otherwise, the loop counter gets 
            ' confused and only every other item is removed. 
            For i = lngCount To 1 Step -1 
                ' Save attachment before deleting from item. 
                ' Get the file name. 
                strFile = "Source.csv" 
                ' Combine with the path to the Temp folder. 
                strFile = strFolderpath & strFile 
                ' Save the attachment as a file. 
                objAttachments.Item(i).SaveAsFile strFile 
                ' Delete the attachment. 
                'write the save as path to a string to add to the message 
                'check for html and use html tags in link 
                If objMsg.BodyFormat <> olFormatHTML Then 
                    strDeletedFiles = strDeletedFiles & vbCrLf & "" 
                    strDeletedFiles = strDeletedFiles & "" & "<a href='//" & _ 
                    strFile & "'>" & strFile & "</a>" 
                End If 
            Next i 
        End If 
    Set objAttachments = Nothing 
    Set objMsg = Nothing 
    Set objOL = Nothing 

   Dim oXLAppSource As Excel.Application ' 
    Dim oXLBookSource As Excel.Workbook 
    Dim oXLSheetSource As Excel.Worksheet 
    Dim rCountSource As Long 
    Dim rangeSource As Range 
    Dim tmpCSV As String 'string to hold the CSV info 
    Dim f As Integer 
' Open Source Workbook and copy the new data 
    Set oXLAppSource = New Excel.Application  'Create a new instance of Excel 
    Set oXLBookSource = oXLAppSource.Workbooks.Open("C:\test\source.csv") 
    Set oXLSheetSource = oXLBookSource.Worksheets(1)  'Work with the first worksheet 
    oXLAppSource.Visible = False               'Show it to the user 
    rCountSource = oXLSheetSource.Range("A" & oXLSheetSource.Rows.Count).End(-4162).Row 
If rCountSource > 1 Then 
    Const CSVFile As String = "C:\test\database.csv" 'replace with your filename
   f = FreeFile
   Open CSVFile For Append As #f 
    tmpCSV = Range2CSV(oXLSheetSource.Range("A2:L" & rCountSource)) 
    Print #f, tmpCSV 
    Close #f 
End If 
  oXLBookSource.Close 1 
End Sub 
Function Range2CSV(list) As String 
Dim tmp As String 
Dim cr As Long 
Dim r As Range 
If TypeName(list) = "Range" Then 
cr = 1 
For Each r In list.Cells 
If r.Row = cr Then 
If tmp = vbNullString Then 
tmp = r.Value 
tmp = tmp & "," & r.Value 
End If 
cr = cr + 1 
If tmp = vbNullString Then 
tmp = r.Value 
tmp = tmp & Chr(10) & r.Value 
End If 
End If 
End If 
Range2CSV = tmp 
End Function
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
I could not open attached files, i receive a message with “corrupted file” Using Outlook 0
T Word attached files open Word. Excel files open Picture Manager!! Using Outlook 2
chummy Open multiple Hyperlinks to download files Outlook VBA and Custom Forms 3
G Get current open draft message body from VBA Outlook VBA and Custom Forms 1
Rupert Dragwater How to get Outlook 365 to open from websites Using Outlook 5
Witzker Outlook 2019 Edit contact from email does not open the user defined contactform Using Outlook 3
S Leaving ActiveExplorer open for editing after Sub is done Outlook VBA and Custom Forms 0
Commodore PDF attachments started to open in Edge Using Outlook 0
T Outlook 2021 Cannot open attachments Outlook DeskTop 2021 Using Outlook 0
X Open Hyperlinks in an Outlook Email Message (Help with Diane's solution) Outlook VBA and Custom Forms 3
S HTML Code Embedded in String Within Open Outlook Email Preventing Replace(Application.ActiveInspector.CurrentItem.HTMLBody From Working Outlook VBA and Custom Forms 4
talla Can't open Outlook Item. Using Outlook 0
O Outlook on Android: after sharing / sending a news article, draft remains open. Why? Using Outlook 1
K Embedded photos no longer open with Photos or Photo Viewer Using Outlook 7
Witzker Open Contact missing in Outlook 2019 Using Outlook 2
L Cannot open PST file for first session each day Using Outlook 6
A How to open a specific link automatically with outlook 2016 Outlook VBA and Custom Forms 6
H Upon opening Outlook, make my popmail inbox open instead of inbox Using Outlook 1
sahameed82 SharePoint calendar directly open in Outlook Using Outlook 0
N .pst archive from work will not open/import on Microsoft 365 Exchange Server Administration 0
C Outlook 2016/2019 hangs after being open for an extended period Using Outlook 4
M Where is the setting to *turn off* open calendar in a new window? Using Outlook 3
W Automatically open attachments without automatically printing them Using Outlook 0
Y Open and Save Hyperlink Files in multiple emails Outlook VBA and Custom Forms 9
J How to open OST file in Outlook 2019 & 2016 Using Outlook 1
C Can't Open Outlook 365 Using Outlook 0
D after delete mail, open the next one Outlook VBA and Custom Forms 0
N VBA Script to Open highlighted e-mail and Edit Message Outlook VBA and Custom Forms 5
M outlook won't open! Using Outlook 1
N Open & Save VBAProject.Otm using VBA Code Outlook VBA and Custom Forms 1
E Unable to open Outlook 2010 after adding new email account Using Outlook 4
M other user's mailbox won't open, forms disappeared Using Outlook 42
S SendFromAccount - Problem trying to test existing value in open email Outlook VBA and Custom Forms 2
J Open an outlook email by Subject on MS Access linked table with VBA Outlook VBA and Custom Forms 10
D Add Tetxbox at form open Outlook VBA and Custom Forms 1
U Catching ModuleSwitch events after "open in new window" Outlook VBA and Custom Forms 2
Andrew Quirl Open attachment, manipulate without add-on program? Outlook VBA and Custom Forms 5
S Reminder Dialog Open Button Using Outlook 2
A How to open a specific link automatically with outlook Outlook VBA and Custom Forms 13
S Outlook 2010 Cannot Open Attachments Using Outlook 14
N open the hyperlink in Outlook directly instead of browser Using Outlook 1
S Outlook does not open the .pst file created by the Outlook Using Outlook 5
A open Outlook with multiple windows. Using Outlook 0
K open calendar from address book Outlook VBA and Custom Forms 1
T Double clik behavior on agenda open a new meeting request Using Outlook 1
E Open olNoteItem Attachment Outlook VBA and Custom Forms 6
G Can't open .pst. Message could not access default folder (Outlook 2010 (.pst). Before that was backi Using Outlook 0
I Outlook 2010, 2013 will not open .msg or .eml files Using accounts in Outlook 1
Diane Poremsky Outlook VBA: Work with Open Item or Selected Item Using Outlook 0
O Windows 10 x64 Outlook 2013 - URL does not open (anymore) Using Outlook 3

Similar threads