vba send email with attachment question

[Pinaceous]

Hi All,

I'm using this code to generate an email to send:

Sub sendemail()
Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object
  
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

        
        With OutMail
            .to = Worksheets("admin").Range("f9").Value
            .CC = Worksheets("admin").Range("f10").Value
            .BCC = ""
            .Subject = Worksheets("admin").Range("i11").Value
            .Body = Worksheets("admin").Range("i12").Value
            
            .display
        End With
End Sub

This code is really working great!

My only question is, How do I automatically attach two files to these generated emails?

Does anyone know where I put this into the code above??

I'm lost!

Many thanks in advance!
Pinaceous

 

[Trebor76]

Hi Pinaceous,

Doesn't seem like you attach multiple files via a single line so have a look at the following where I created an array of files to be attached which the code loops through and attaches each one (I also included code for attaching a sing file for reference):

Option Explicit
Sub sendemail()

    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim varMyAttachment As Variant
  
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
        
    With OutMail
        .To = Worksheets("admin").Range("f9").Value
        .CC = Worksheets("admin").Range("f10").Value
        .BCC = ""
        .Subject = Worksheets("admin").Range("i11").Value
        .Body = Worksheets("admin").Range("i12").Value
        'For a single file:
        .Attachments.Add ("C:\File1.xlsb")
        'For multiple files:
        For Each varMyAttachment In Array("C:\File1.xlsb", "C:\File2.xlsb")
            .Attachments.Add varMyAttachment
        Next varMyAttachment
        .Display
    End With
    
End Sub

Regards,

Robert

 

[Pinaceous]

Hey Robert,

Many thanks for helping me out with the code!

You have provided all that I need to test this code out.

Really appreciate it!

Thanks,
Paul

 

[Pinaceous]

Hey Robert,

Would you know how I can change the font color in this code??

It is currently coming up a blue, even before you posted.

Is it in the code or do you think it is with my settings?

Thanks,
Paul

 

[Trebor76]

I haven't done this for a while but from memory if you want to format the body of an email I think you have to use the HTMLBody property in your code and use tags. It is a little tricky / finicky.

Probably best to start a new thread as this is a totally different question from what you've originally asked here.

Robert

 

[Pinaceous]

Hi Robert!

I do appreciate the feedback and your posts.

But before I go if there is an error debug happening on this line of the code:

  Set OutMail = OutApp.CreateItem(0)

Do you think it is because I need to adjust the settings in outlook?

Thanks again,
Paul

 

[Trebor76]

But before I go if there is an error debug happening on this line of the code:

But you said the "code is really working great!"? It works fine for me

What started the error to occur?

 

[Pinaceous]

The code is working great, let me check out my email settings. Thanks again for everything Rob.

 

[Trebor76]

NP. If the error is "variable not defined" that would be because I included Option Explicit and one or more of variables aren't defined?

 

[Pinaceous]

Good point. Let me check it out and I'll have to get back to you. Thanks again for everything