Outlook - count number of emails in Sent Items folder and spit out results in Excel

[gmayor]

The add-in should ignore any e-mail address that looks like *@yourdomain.com" did the e-mail address you sent to have such a domain name?

[vhrame]

Ok so I found out that my the internal email address is actually something else other than what is shown, so i added that in to exclude and it worked! yay!!

[vhrame]

I am now working on trying to see if i can update this excel file which has been password protected. I know there is a way to unprotect and protect a file within the VBA code in excel, but I am trying to see if i can do it via Outlook. Graham, thanks for your help :-)

[gmayor]

I am now working on trying to see if i can update this excel file which has been password protected. I know there is a way to unprotect and protect a file within the VBA code in excel, but I am trying to see if i can do it via Outlook. Graham, thanks for your help :-)

You cannot use ADODB to write to a password protected workbook that isn't open in Excel, so one solution would be to open it e.g. as follows. As it is then open you could write directly to it, but you can still use ADODB as it is likely to be faster. If the workbook isn't already open, the process will open it with the indicated passwords. You will of course need to change those as appropriate.

Private Function WriteToWorksheet(strWorkbook As String, _                                  strRange As String, _
                                  strValues As String)
Dim ConnectionString As String
Dim strSQL As String
Dim CN As Object
Dim xlApp As Object
Dim xlWB As Object
Dim vWB As Variant
Dim bXLStarted As Boolean, bOpen As Boolean
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Set xlApp = CreateObject("Excel.Application")
        bXLStarted = True
    End If


    vWB = Split(strWorkbook, "\")
    For Each xlWB In xlApp.workbooks
        If xlWB.Name = vWB(UBound(vWB)) Then
            bOpen = True
            Exit For
        End If
    Next xlWB
    If Not bOpen Then
        Set xlWB = xlApp.workbooks.Open(fileName:=strWorkbook, _
                                        Password:="abc123", _
                                        WriteResPassword:="abc123", _
                                        IgnoreReadOnlyRecommended:=True)
    End If
    On Error GoTo 0
    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                       "Data Source=" & strWorkbook & ";" & _
                       "Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"
    strSQL = "INSERT INTO [" & strRange & "$] VALUES('" & strValues & "')"
    Set CN = CreateObject("ADODB.Connection")
    Call CN.Open(ConnectionString)
    Call CN.Execute(strSQL, , 1 Or 128)
    CN.Close
    If bOpen = False Then
        xlWB.Close 1
    Else
        xlWB.Save
    End If
    If bXLStarted = True Then xlApp.Quit
lbl_Exit:
    Set CN = Nothing
    Set xlApp = Nothing
    Set xlWB = Nothing
    Exit Function
End Function