Matt Salas
New Member
- Joined
- Jan 8, 2021
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
First time posting, so I hope I'm doing it right. I have a macro to copy 2 specific pages from a macro-enabled workbook, prompt the user to save the workbook in a file location with a specific name from referenced cells within the new workbook and attached the new active workbook to an email. However, the file is attached as a .zip file, not .xls. What am I doing wrong?
Code:
fname = Application.GetSaveAsFilename(InitialFileName:="VAR Qualification form for " & Range("B8").Value & "," & " LT# " & ThisWorkbook.Sheets("VAR Information").Range("C2").Value, fileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Save As")
If fname = False Then Exit Sub
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
StrBody = "<BODY style=font-size:12pt;font-family:calibri><font color=334d99>Hello,<br><br>Will you please process the attached VAR qualification form? Thanks!!"
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
With OutlookMail
.Display
.To = "test@@test.com"
.CC = ""
.BCC = ""
.Subject = "VAR qualification for " & ThisWorkbook.Sheets("VAR Information").Range("B8").Value & "," & " LT# " & ThisWorkbook.Sheets("VAR Information").Range("C2").Value
.HTMLBody = StrBody & .HTMLBody
.Attachments.Add ActiveWorkbook.FullName
End With
Application.DisplayAlerts = True
ActiveWorkbook.Close False
End Sub
I've tried a simpler form of the macro just sending an active workbook with the same results, leading me to believe isn't a security setting of some sort rather that an issue with the macro
Code:
Sub SendWorkBook()
Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
On Error Resume Next
With OutlookMail
.Display
.To = "test@extendoffice.com"
.CC = ""
.BCC = ""
.Subject = "VAR qualification for " & ThisWorkbook.Sheets("VAR Information").Range("B8").Value & "," & " LT# " & ThisWorkbook.Sheets("VAR Information").Range("C2").Value
.Body = "Hello, please check and read this document, thank you."
.Attachments.Add Application.ActiveWorkbook.FullName
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub
Code:
fname = Application.GetSaveAsFilename(InitialFileName:="VAR Qualification form for " & Range("B8").Value & "," & " LT# " & ThisWorkbook.Sheets("VAR Information").Range("C2").Value, fileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Save As")
If fname = False Then Exit Sub
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
StrBody = "<BODY style=font-size:12pt;font-family:calibri><font color=334d99>Hello,<br><br>Will you please process the attached VAR qualification form? Thanks!!"
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
With OutlookMail
.Display
.To = "test@@test.com"
.CC = ""
.BCC = ""
.Subject = "VAR qualification for " & ThisWorkbook.Sheets("VAR Information").Range("B8").Value & "," & " LT# " & ThisWorkbook.Sheets("VAR Information").Range("C2").Value
.HTMLBody = StrBody & .HTMLBody
.Attachments.Add ActiveWorkbook.FullName
End With
Application.DisplayAlerts = True
ActiveWorkbook.Close False
End Sub
I've tried a simpler form of the macro just sending an active workbook with the same results, leading me to believe isn't a security setting of some sort rather that an issue with the macro
Code:
Sub SendWorkBook()
Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
On Error Resume Next
With OutlookMail
.Display
.To = "test@extendoffice.com"
.CC = ""
.BCC = ""
.Subject = "VAR qualification for " & ThisWorkbook.Sheets("VAR Information").Range("B8").Value & "," & " LT# " & ThisWorkbook.Sheets("VAR Information").Range("C2").Value
.Body = "Hello, please check and read this document, thank you."
.Attachments.Add Application.ActiveWorkbook.FullName
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub