vba send email with attachment question

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
657
Hi All,

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

Code:
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
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):

Code:
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

Well-known Member
Joined
Jun 11, 2014
Messages
657
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

Well-known Member
Joined
Jun 11, 2014
Messages
657
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,714

ADVERTISEMENT

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

Well-known Member
Joined
Jun 11, 2014
Messages
657
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:

Code:
  Set OutMail = OutApp.CreateItem(0)

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

Thanks again,
Paul
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714

ADVERTISEMENT

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 :confused:

What started the error to occur?
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
657
The code is working great, let me check out my email settings. Thanks again for everything Rob.
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
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

Well-known Member
Joined
Jun 11, 2014
Messages
657
Good point. Let me check it out and I'll have to get back to you. Thanks again for everything :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,666
Messages
5,597,456
Members
414,145
Latest member
lonnie451

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
Top