foxyginger
09-12-2017, 08:55 AM
My created MACRO (shown below) runs perfectly, but when the recipient opens the e-mail(s) the excel file will not open and comes up with the error message below. Any suggestions on troubleshooting?
20313
Dim ExcelFile As StringDim PDFfile As String
Sub ProcessEquityETLandEmail()
Call IfAnalysis
End Sub
Sub IfAnalysis()
If (Cells(12, 6) = 1) Then
Call CreateExcelandEmail
Call CreatePDFandEmailwithExcel
Else
Call CreateExcelandEmail
End If
End Sub
Sub CreatePDFandEmailwithExcel()
Workbooks("Revised equity transaction request form.xlsm").Activate
ThisWorkbook.Sheets("Equity Wire").Activate
ActiveWindow.SmallScroll Down:=36
Range("B47:J77").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Columns("A:I").Select
Columns("A:I").EntireColumn.AutoFit
Range("A3:I30").Select
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("B5").Select
Selection.NumberFormat = "m/d/yyyy"
Range("C1").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Font
.Name = "Calibri"
.Size = 13
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With Range("F4:I31").Select
Selection.Style = "Comma"
Range("F3:F30").Select
Selection.Style = "Percent"
Range("K5").Select
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveWindow.View = xlNormalView
ActiveWindow.SmallScroll Down:=-12
End With
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String
Path = "M:\"
FileName1 = Range("B3")
FileName2 = Format(Range("B4").Value, "mm-dd-yyyy")
FileName3 = "Wire Information"
PDFfile = Path & FileName1 & " " & FileName2 & " " & FileName3 & ".pdf"
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object
Set OutLookApp = CreateObject("OutLook.Application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments
With OutLookMailItem
.To = "GAM Cash Management"
.Subject = "Equity ETL and Wire Information"
.Body = "Please wire the attached Equity ETL." & vbNewLine & vbNewLine & "Thank you,"
myAttachments.Add PDFfile
myAttachments.Add ExcelFile
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub
Sub CreateExcelandEmail()
Sheets("ETL").Select
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Range("C7").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DATA"
Application.CutCopyMode = False
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String
Path = "M:\"
FileName1 = Range("C3")
FileName2 = Format(Range("D3").Value, "mm-dd-yyyy")
FileName3 = "Equity ETL"
ExcelFile = Path & FileName1 & " " & FileName2 & " " & FileName3 & ".xlsx"
ActiveWorkbook.SaveAs Filename:=ExcelFile, FileFormat:=xlOpenXMLWorkbook = 51
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object
Set OutLookApp = CreateObject("OutLook.Application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments
With OutLookMailItem
.To = " "
.Subject = "Equity ETL"
.Body = "Please process the attached Equity ETL." & vbNewLine & vbNewLine & "Thank you,"
myAttachments.Add ExcelFile
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub
20313
Dim ExcelFile As StringDim PDFfile As String
Sub ProcessEquityETLandEmail()
Call IfAnalysis
End Sub
Sub IfAnalysis()
If (Cells(12, 6) = 1) Then
Call CreateExcelandEmail
Call CreatePDFandEmailwithExcel
Else
Call CreateExcelandEmail
End If
End Sub
Sub CreatePDFandEmailwithExcel()
Workbooks("Revised equity transaction request form.xlsm").Activate
ThisWorkbook.Sheets("Equity Wire").Activate
ActiveWindow.SmallScroll Down:=36
Range("B47:J77").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Columns("A:I").Select
Columns("A:I").EntireColumn.AutoFit
Range("A3:I30").Select
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("B5").Select
Selection.NumberFormat = "m/d/yyyy"
Range("C1").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Font
.Name = "Calibri"
.Size = 13
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With Range("F4:I31").Select
Selection.Style = "Comma"
Range("F3:F30").Select
Selection.Style = "Percent"
Range("K5").Select
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveWindow.View = xlNormalView
ActiveWindow.SmallScroll Down:=-12
End With
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String
Path = "M:\"
FileName1 = Range("B3")
FileName2 = Format(Range("B4").Value, "mm-dd-yyyy")
FileName3 = "Wire Information"
PDFfile = Path & FileName1 & " " & FileName2 & " " & FileName3 & ".pdf"
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object
Set OutLookApp = CreateObject("OutLook.Application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments
With OutLookMailItem
.To = "GAM Cash Management"
.Subject = "Equity ETL and Wire Information"
.Body = "Please wire the attached Equity ETL." & vbNewLine & vbNewLine & "Thank you,"
myAttachments.Add PDFfile
myAttachments.Add ExcelFile
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub
Sub CreateExcelandEmail()
Sheets("ETL").Select
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Range("C7").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DATA"
Application.CutCopyMode = False
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String
Path = "M:\"
FileName1 = Range("C3")
FileName2 = Format(Range("D3").Value, "mm-dd-yyyy")
FileName3 = "Equity ETL"
ExcelFile = Path & FileName1 & " " & FileName2 & " " & FileName3 & ".xlsx"
ActiveWorkbook.SaveAs Filename:=ExcelFile, FileFormat:=xlOpenXMLWorkbook = 51
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object
Set OutLookApp = CreateObject("OutLook.Application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments
With OutLookMailItem
.To = " "
.Subject = "Equity ETL"
.Body = "Please process the attached Equity ETL." & vbNewLine & vbNewLine & "Thank you,"
myAttachments.Add ExcelFile
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub