I have a excel file named "buttons" which has just buttons to run the macros stored in "buttons" file, Im trying to open another file and send the sheets of another file as outlook email.
The issue is, the macro mails the sheets of "buttons" file itself instead of sending the sheets of selected file through vba. attaching the code below
Sub mail_every_sheet()
Dim File_text As Variant
File_text = Application.GetOpenFilename("All xlsx files (*.xlsx*), *.xlsx", , "Select Your File")
Workbooks.Open Filename:=File_text, UpdateLinks:=False
Dim xWb As Workbook
Dim xFileExt As String
Dim xFileFormatNum As Long
Dim xTempFilePath As String
Dim xFileName As String
Dim xOlApp As Object
Dim xMailObj As Object
Dim CurrDate As String
CurrDate = Format(Date, "MM-DD-YY")
On Error Resume Next
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
xTempFilePath = Environ$("temp") & "\"
If Val(Application.Version) < 12 Then
xFileExt = ".xls": xFileFormatNum = -4143
Else
xFileExt = ".xlsm": xFileFormatNum = 52
End If
Set xOlApp = CreateObject("Outlook.Application")
For Each xWs In ThisWorkbook.Worksheets
If xWs.Name <> "mastersheet" Then
xWs.Copy
Set xWb = ActiveWorkbook
xFileName = xWs.Name & " " & CurrDate
Set xMailObj = xOlApp.CreateItem(0)
xWb.Sheets.Item(1).Range("C2").Value = ""
With xWb
.SaveAs xTempFilePath & xFileName & xFileExt, FileFormat:=xFileFormatNum
With xMailObj
'specify the CC, BCC, Subject, Body below
.To = xWs.Name & "@gmail.com"
.CC = ""
.BCC = ""
.Subject = "enter subject"
.body = "enter body "
.Attachments.Add xWb.FullName
.Display
End With
.Close SaveChanges:=False
End With
Set xMailObj = Nothing
Kill xTempFilePath & xFileName & xFileExt
End If
Next
Set xOlApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
This code must work on selected file using Application.GetOpenFilename, but it runs on "buttons" file which contains this code.
any idea how to send the sheets as mail of the file selected by the user, Thanks in advance
The issue is, the macro mails the sheets of "buttons" file itself instead of sending the sheets of selected file through vba. attaching the code below
Sub mail_every_sheet()
Dim File_text As Variant
File_text = Application.GetOpenFilename("All xlsx files (*.xlsx*), *.xlsx", , "Select Your File")
Workbooks.Open Filename:=File_text, UpdateLinks:=False
Dim xWb As Workbook
Dim xFileExt As String
Dim xFileFormatNum As Long
Dim xTempFilePath As String
Dim xFileName As String
Dim xOlApp As Object
Dim xMailObj As Object
Dim CurrDate As String
CurrDate = Format(Date, "MM-DD-YY")
On Error Resume Next
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
xTempFilePath = Environ$("temp") & "\"
If Val(Application.Version) < 12 Then
xFileExt = ".xls": xFileFormatNum = -4143
Else
xFileExt = ".xlsm": xFileFormatNum = 52
End If
Set xOlApp = CreateObject("Outlook.Application")
For Each xWs In ThisWorkbook.Worksheets
If xWs.Name <> "mastersheet" Then
xWs.Copy
Set xWb = ActiveWorkbook
xFileName = xWs.Name & " " & CurrDate
Set xMailObj = xOlApp.CreateItem(0)
xWb.Sheets.Item(1).Range("C2").Value = ""
With xWb
.SaveAs xTempFilePath & xFileName & xFileExt, FileFormat:=xFileFormatNum
With xMailObj
'specify the CC, BCC, Subject, Body below
.To = xWs.Name & "@gmail.com"
.CC = ""
.BCC = ""
.Subject = "enter subject"
.body = "enter body "
.Attachments.Add xWb.FullName
.Display
End With
.Close SaveChanges:=False
End With
Set xMailObj = Nothing
Kill xTempFilePath & xFileName & xFileExt
End If
Next
Set xOlApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
This code must work on selected file using Application.GetOpenFilename, but it runs on "buttons" file which contains this code.
any idea how to send the sheets as mail of the file selected by the user, Thanks in advance