Hi Guys,
I am currently using a macro which will send the working sheet as attachment in mail, but i also wanted the attachment to get saved in the specific folder in my local.Below is my current code , it will be really helpful if i can have the code for saving the attachment in local folder.
Local folder path : C:\Users\xyz\Desktop\macro
I am currently using a macro which will send the working sheet as attachment in mail, but i also wanted the attachment to get saved in the specific folder in my local.Below is my current code , it will be really helpful if i can have the code for saving the attachment in local folder.
Local folder path : C:\Users\xyz\Desktop\macro
Code:
Sub SendMail()
'Turn off screen updating
Application.ScreenUpdating = False
'Activate the correct sheet in the workbook
ActiveWorkbook.Sheets("Catalog Integration Formatter").Activate
'EMAIL SHEET AS ATTACHMENT
Dim LWorkbook As Workbook
Dim LFileName As String
Dim Provider As String
Dim Delim As String
Dim file_date As String
Dim subject_date As String
Dim Build As String
Dim Marketplace As String
Dim emailString As String
Provider = Range("D1").Value
Delim = " - "
file_date = Format(Now, "MMM,d,yyyy")
subject_date = Now()
Build = Range("F3").Value
Marketplace = Range("F4").Value
emailString = Range("L2").Value
' Add current time to Date cell
Range("F5") = Format(subject_date, "mm/dd/yyyy h:mm AM/PM")
'Align the columns pasted in from the CSV file
Range("E13:E32,E35:E54,E57:E76,E79:E98").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Range("F13:F32,F35:F54,F57:F76,F79:F98,H13:H32,H35:H54,H57:H76,H79:H98").Select
With Selection
.VerticalAlignment = xlCenter
.WrapText = True
End With
Range("D1").Select
'Copy the active worksheet and save to a temporary workbook
ActiveSheet.Copy
Set LWorkbook = ActiveWorkbook
'Create a temporary file in your current directory that uses the name of the sheet as the filename
LFileName = Provider & Delim & file_date
On Error Resume Next
'Delete the file if it already exists
Kill LFileName
On Error GoTo 0
'Save temporary file
LWorkbook.SaveAs Filename:=LFileName, FileFormat:=52
' Select the table (or range of cells) on the active worksheet.
ActiveSheet.Columns("D:J").Select
' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = False
' Set email destination, subject line and send
With ActiveSheet.MailEnvelope
.Item.Subject = Provider & Delim & Marketplace & Delim & Build & Delim & subject_date
.Item.To = emailString
.Item.Attachments.Add ActiveWorkbook.FullName
.Item.Send
End With
'Delete the temporary file and close temporary Workbook
LWorkbook.ChangeFileAccess Mode:=xlReadOnly
Kill LWorkbook.FullName
LWorkbook.Close SaveChanges:=False
'Turn back on screen updating
Application.ScreenUpdating = True
End Sub
Last edited: