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

Status
Not open for further replies.

Deniz

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

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.

/deniz
 
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.

Code:
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 
    Next 
ExitSub: 
    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. 
                objAttachments.Item(i).Delete 
                '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 & "" 
                    Else 
                    strDeletedFiles = strDeletedFiles & "" & "<a href='//" & _ 
                    strFile & "'>" & strFile & "</a>" 
                End If 
            Next i 
        End If 
      
       objMsg.Save 
        objMsg.Delete 
   
 
ExitSub: 
    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 
  oXLAppSource.Quit 
 
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 
Else 
tmp = tmp & "," & r.Value 
End If 
Else 
cr = cr + 1 
If tmp = vbNullString Then 
tmp = r.Value 
Else 
tmp = tmp & Chr(10) & r.Value 
End If 
End If 
Next 
End If 
 
Range2CSV = tmp 
End Function
 
Status
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
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 outlook.com 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 Outlook.com 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
Hudas VBA find and open an email without looping thru each email in the inbox Outlook VBA and Custom Forms 1

Similar threads

Back
Top