Download attached files from Gmail by VBA (Excel 2016)

K

kfschaefer1

New Member
Joined
Feb 28, 2016
Messages
9
My company is currently using GMAIL, I do not have access to Outlook. I currently have VBA code the generates Gmail emails. I was hoping that I may be able to modify this in some way so that I can grab email attachments from a specific email. I get several gmail emails with attachments that currently need to be manually downloaded then moved to the various files. I may even consider add-in for Excel.

After researching the web without much success, I am turning to you guys to point me in the right direction.

Here is my current code:

Code: 
[COLOR=#101094][FONT=Consolas]Public[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Email[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]()[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#303336]
[/COLOR][COLOR=#101094]On[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Error[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]GoTo[/COLOR][COLOR=#303336] Email_Error

[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] iMsg [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Object[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] iConf [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Object[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] strbody [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] strSubject [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] Flds [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Variant[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] FilePath [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] FilePath1 [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] FilePath2 [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] nMth [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] curDate [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] nDate [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] nName [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] strContact [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] strTo [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] strFrom [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] aCell [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Range
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] bolFound [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Boolean[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] ws [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Worksheet

nDate [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Format[/COLOR][COLOR=#303336]([/COLOR][COLOR=#101094]Date[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"mmddyyyy"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
nMth [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Format[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]DateSerial[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Year[/COLOR][COLOR=#303336]([/COLOR][COLOR=#101094]Date[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] Month[/COLOR][COLOR=#303336]([/COLOR][COLOR=#101094]Date[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]-[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"mmm yyyy"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
curDate [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Format[/COLOR][COLOR=#303336]([/COLOR][COLOR=#101094]Date[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"mmm dd, yyyy"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]


bolFound [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Each[/COLOR][COLOR=#303336] ws [/COLOR][COLOR=#101094]In[/COLOR][COLOR=#303336] ThisWorkbook[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Worksheets
    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Name [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"ContactList"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336] bolFound [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]True[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336] ws
[/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] bolFound [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
    MsgBox [/COLOR][COLOR=#7D2727]"Couldn't find the required sheet."[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] Chr[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]10[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
        [/COLOR][COLOR=#7D2727]"Aborting..."[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] ThisWorkbook[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Worksheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"ContactList"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
     [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Each[/COLOR][COLOR=#303336] aCell [/COLOR][COLOR=#101094]In[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"A:E"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] aCell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Offset[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value2 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"M2"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value2 [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
            nName [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] aCell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Offset[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value2
            nName [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ReplaceAll[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]nName[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]" "[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]

            [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] aCell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Offset[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value2 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"From"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
                strFrom [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] aCell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Offset[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]3[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value2
            [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]

            [/COLOR][COLOR=#101094]Select[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Case[/COLOR][COLOR=#303336] aCell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Offset[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value2
                [/COLOR][COLOR=#101094]Case[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"Val"[/COLOR][COLOR=#303336]
                    strTo [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"'"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] aCell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Offset[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]3[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value2 [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"'"[/COLOR][COLOR=#303336]
                    strContact [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] aCell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Offset[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]4[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value2
                   [/COLOR][COLOR=#101094]GoTo[/COLOR][COLOR=#303336] cont[/COLOR][COLOR=#303336]:[/COLOR][COLOR=#303336]
                [/COLOR][COLOR=#101094]Case[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"Invoice"[/COLOR][COLOR=#303336]
                    strTo [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] strTo [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"'"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] aCell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Offset[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]3[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value2 [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
                        [/COLOR][COLOR=#7D2727]";"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"'"[/COLOR][COLOR=#303336]
            [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Select[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336] aCell
 cont[/COLOR][COLOR=#303336]:[/COLOR][COLOR=#303336]
 [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]

  nName [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"MainForm"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"B3"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value2 [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"MainForm"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"D3"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value2
FilePath [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]""[/COLOR][COLOR=#303336]
FilePath [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"MainForm"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"B3"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"\"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] nMth [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"\"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
    nName [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] ReplaceAll[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]nMth[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]" "[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"Invoice.pdf"[/COLOR][COLOR=#303336]

FilePath1 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] FilePath [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"\"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] nName [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] ReplaceAll[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]nMth[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]" "[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
    [/COLOR][COLOR=#7D2727]"_"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"Invoice.pdf"[/COLOR][COLOR=#303336]
FilePath2 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] FilePath [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"\"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] nName [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] nDate [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_Invoice.xlsx"[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] FileFolderExists[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]FilePath[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
            CreateNewDirectory [/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]FilePath[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] Dir[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]FilePath1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] vbDirectory[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] vbNullString [/COLOR][COLOR=#101094]Or[/COLOR][COLOR=#303336] Dir[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]FilePath2[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] _
            vbDirectory[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] vbNullString [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
            [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] _
                MsgBox[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"File not found, do you wish to create the files for   "[/COLOR][COLOR=#303336] _
                [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] nName [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] nDate [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_Invoice.xlsx and "[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] nName [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_"[/COLOR][COLOR=#303336] _
                [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] ReplaceAll[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]nMth[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]" "[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"Invoice.pdf ?"[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] _
                vbYesNo[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"Create New File"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] vbYes [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
                [/COLOR][COLOR=#858C93]'SaveFinalData[/COLOR][COLOR=#303336]
                CreatePDF
            [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] iMsg [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] CreateObject[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"CDO.Message"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] iConf [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] CreateObject[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"CDO.Configuration"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]

iConf[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Load [/COLOR][COLOR=#303336]-[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336] [/COLOR][COLOR=#858C93]' CDO Source Defaults[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] Flds [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] iConf[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Fields
    [/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] Flds
        [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Item[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"http://schemas.microsoft.com/cdo/configuration/smtpusessl"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] _
            [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]True[/COLOR][COLOR=#303336]
             [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Item[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] _
            [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Item[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"http://schemas.microsoft.com/cdo/configuration/sendusername"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] _
            [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] strFrom
        [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Item[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"http://schemas.microsoft.com/cdo/configuration/smtpserver"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] _
            [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"aspmx.l.google.com"[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Item[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"http://schemas.microsoft.com/cdo/configuration/sendusing"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] _
            [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Item[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"http://schemas.microsoft.com/cdo/configuration/smtpserverport"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] _
            [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]25[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Update
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]
 [/COLOR][COLOR=#858C93]'Sends Validation Email prior to emailing Invoice to Customer[/COLOR][COLOR=#303336]
 [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] ActiveSheet[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]OLEObjects[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"CheckBox1"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#101094]Object[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]True[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
    strSubject [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"Quotewizard"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]" "[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] nMth [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]", Invoice for "[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
        ReplaceAll[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]nName[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_"[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]" "[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]" Review"[/COLOR][COLOR=#303336]
    strbody [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] strContact [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]","[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] vbNewLine [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] vbNewLine [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
        [/COLOR][COLOR=#7D2727]"Please find attached the invoice for"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]" "[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] nMth [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"."[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
        vbNewLine [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] vbNewLine [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
        [/COLOR][COLOR=#7D2727]"Please validate changes and indicate any discrepancies."[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
        vbNewLine [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] vbNewLine [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"Thank you,"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] vbNewLine [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] vbNewLine [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
        [/COLOR][COLOR=#7D2727]"Karen"[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Else[/COLOR][COLOR=#303336]
    strSubject [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"Quotewizard"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]" "[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] nMth [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]", Invoice for "[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] nName _
        [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]""[/COLOR][COLOR=#303336]
    strbody [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"Please find attached the invoice for"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]" "[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] nMth [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"."[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
        vbNewLine [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] vbNewLine [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
        [/COLOR][COLOR=#7D2727]"Should you have any questions please contact your account representative."[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
        vbNewLine [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] vbNewLine [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"Thank you,"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] vbNewLine [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] vbNewLine [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
        [/COLOR][COLOR=#7D2727]"KFS & vbNewLine & _
        "[/COLOR][COLOR=#303336]Financial Operations Analyst[/COLOR][COLOR=#7D2727]"

End If

On Error GoTo 0
    If Len(strTo) > 0 Then
        Set iMsg = CreateObject("[/COLOR][COLOR=#303336]CDO[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Message[/COLOR][COLOR=#7D2727]")
            With iMsg
                 Set .Configuration = iConf
                .TO = strFrom 'strTo
                .From = strFrom
                .Subject = strSubject
                .TextBody = strbody
                'Attaches the PDF file to Email
                 .AddAttachment FilePath1
                'Attacheds the xlsx file to Email
                .AddAttachment FilePath2
                .Send
               ' .Display
            End With
        Set iMsg = Nothing
    End If

On Error GoTo 0
Exit Sub

 Email_Error:

 MsgBox "[/COLOR][COLOR=#101094]Error[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]" & Err.Number & "[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]" & Err.Description & "[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]in[/COLOR][COLOR=#303336] procedure Email of [/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336] modSaveFinalData[/COLOR][COLOR=#7D2727]"
 [/COLOR]</code>[COLOR=#7D2727][FONT=Consolas] End Sub[/FONT][/COLOR]
 
Last edited:
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

R
  • Solved
Sending gmail email through Excel (VBA)
Replies
6
Views
10K
kavemankev
K
B
  • Solved
Unable to attach file using CDO mail
Replies
2
Views
2K
bblevins
B
D
  • Question Question
Attatch multiple files form a folder to a e-mail(gmail) using VBA
Replies
2
Views
1K
CalvinGamble
C
F
  • Solved
Sending Comcast email without outlook
Replies
9
Views
957
Trevil
T
J
  • Solved
Curious problem, excel creates a PDF (with data), then when sending as attachment, PDF is BLANK
Replies
3
Views
1K
Micron
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top