Attach an excel file automatically to email with VBA

A

audrey

Active Member
Joined
Jul 30, 2008
Messages
491
Hello,

i was wondering if the following can be simplified using VBA ?

Action
I have two tabs in my excel spreadsheet. Tab1 and Tab2.
In Tab2, Cell A1, I type "USA"

VBA
The VBA should then filter only "USA" in Column C in Tab1
Remove the other rows in Tab1 which is not "USA"
Attach the Tab1 to Outlook as "xlsx"
with an email body text "Please see attached"
While doing it, I also do not want to lose the data in Tab1 ?


Thank you very much already!
Aude
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
any help ? I have a vba code to do this in PDF, but I dont know how to attach filtered excel sheet
 
Upvote 0
Just finished writing the whole code and documenting it for you. Here is the code. Your second thread says it is PDF but your first says it is to be saved as XLSX.

Sub filterAndEMail()
'Declare cell A1 to use as criteria for filter
Dim cell As Variant
cell = Sheets("Sheet1").Range("A1")
'Activate sheet 2
Sheets("Sheet2").Activate
'Check to see if a filter is on
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
'Apply a filter to column C - Change this to your column
Range("A1").AutoFilter field:=3, Criteria1:=cell
'Seleect the visible cells
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Copy
'Add a workbook
Workbooks.Add
'Paste the data
ActiveSheet.Paste
'Save the workbook - Change the File path and workbook name
ActiveWorkbook.SaveAs "C:\VBA\USA.XLSX"
'Close the workbook
ActiveWorkbook.Close
'Now activate Outlook
Dim olApp As Object
Dim olMail As Object
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)
'Display the email
With olMail
.Display
.To = "Who is the email going to"
.Subject = "Here is the data"
.Body = "Here is the current data"
.Attachments.Add "C:\vba\usa.xlsx" 'Change accordingly
End With
End Sub
Click to expand...
 
Upvote 0
Hi healey, it works perfect thank you... A few questions tho...

1. how can I enhance the body text ? (i.e font calibri, size 11, paragraphs etc..)
2. the attached excel sheet, can it be in the same format (column, row size, font etc..) as the source document ?
 
Upvote 0
The email body would need to be HTMLBody so I have changed this as follows:

With olMail
.Display
.To = "Who is the email going to"
.Subject = "Here is the data"
.htmlBody = "<BODY style=font-size:11pt;font-family:Calibri>Good Morning;<p>"
.Attachments.Add "C:\vba\usa.xlsx" 'Change accordingly
End With
Click to expand...

As to the formatting you would need to let me know what is what or record a macro to give you the formatting you want. When it copies over to the new workbook it should take the font and colours etc so it should just be the column widths and row heights to sort out. Again I suggest you record a macro to give you the code.

I have just realised the HTML code gets replaced on the board, so look at this line

"!BODY style=font-size:11pt;font-family:Calibri?Good Morning;!p?"
Click to expand...

and change each ! to <
and change each ? to >
Click to expand...
 
Last edited:
Upvote 0
hi, when I press the VBA button twice. I also get a debug error

Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Copy
 
Upvote 0
I had this formatting in mind. all calibri Body 11

Good Morning,

Paragraph1

Paragraph2

Paste as picture (Predefined range abc)

Paragraph3

Outlook signature
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Workbook in attachement of Email - specific Xls format . How to ?
Replies
1
Views
276
Fluff
Fluff
The Ruff Report
  • Question
Add multiple attachments from email list
Replies
1
Views
269
Trebor76
T
larinda4
  • Question
VBA: Send Outlook Email & Attach PDF
Replies
1
Views
100
Trebor76
T
V
  • Question
Excel for Mac 2021 v16.81. Send bulk email with an attachmen
Replies
2
Views
260
vk2023
V
G
  • Question
Automatically Send Emails everyday from Excel Based on Cell date comes to today date
Replies
5
Views
449
ganesh0604
G

Forum statistics

Threads
1,214,599
Messages
6,120,453
Members
448,967
Latest member
grijken

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