foxyginger
09-07-2017, 12:12 PM
WARNING: I'm BRAND NEW to VBA
I'm running into an issue with my code with compile error: user-defined type not defined and I'm not sure what that means exactly, but when I go to Tools and References, I have all of the 'Microsoft ActiveX' items selected as well as all of the 'Microsoft Outlook' items to be sure this can run properly, since I don't know which ones exactly to use.
If this code was working properly it would copy specified information from one workbook and paste it to a new workbook, then SaveAs with a FileName using the cell content from "B3" and "B4" and convert it to PDF.
Ex.) If cell "B3" was 1234 and cell "B4" was 09/07/2017, then it would be: 1234 09-07-2017.pdf
Finally, it would attach the newly created PDF to an Outlook e-mail.
The error is in line "Dim FileName1 As String" -- I'm not sure how else to write the code so it SavesAs with the FileName using cell content
Sub WireTransferInfoPDF_EmailAttachment()'
' WireTransferInfoPDF_EmailAttachment Macro
' Copies the information in the Wire Transfer area to a new workbook, converts the workbook to PDF, SaveAs to the users M: drive, and attaches the newly created PDF to an E-mail.
ActiveWindow.SmallScroll Down:=36
Range("B47:J76").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:I28").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("B4").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
Range("C6").Select
Dim Path As String
Dim FileName1 As Sting '"User defined type not defined" Error Message
Dim FileName2 As String
Path = "M:\"
FileName1 = Range("B3")
FileName2 = Format(Range("B4").Value, "mm-dd-yyyy")
AttFile = Path & FileName3 & FileName4 & ".pdf"
ActiveWorkbook.SaveAs Filename:=AttFile, FileFormat:=xlTypePDF, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
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 = "Hi," & vbNewLine & vbNewLine & "Please process the attached Equity ETL." & vbNewLine & vbNewLine & "Thank you,"
myAttachments.Add AttFile
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
Windows("Updated equity transaction request form.xlsm").Activate
End Sub
I'm running into an issue with my code with compile error: user-defined type not defined and I'm not sure what that means exactly, but when I go to Tools and References, I have all of the 'Microsoft ActiveX' items selected as well as all of the 'Microsoft Outlook' items to be sure this can run properly, since I don't know which ones exactly to use.
If this code was working properly it would copy specified information from one workbook and paste it to a new workbook, then SaveAs with a FileName using the cell content from "B3" and "B4" and convert it to PDF.
Ex.) If cell "B3" was 1234 and cell "B4" was 09/07/2017, then it would be: 1234 09-07-2017.pdf
Finally, it would attach the newly created PDF to an Outlook e-mail.
The error is in line "Dim FileName1 As String" -- I'm not sure how else to write the code so it SavesAs with the FileName using cell content
Sub WireTransferInfoPDF_EmailAttachment()'
' WireTransferInfoPDF_EmailAttachment Macro
' Copies the information in the Wire Transfer area to a new workbook, converts the workbook to PDF, SaveAs to the users M: drive, and attaches the newly created PDF to an E-mail.
ActiveWindow.SmallScroll Down:=36
Range("B47:J76").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:I28").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("B4").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
Range("C6").Select
Dim Path As String
Dim FileName1 As Sting '"User defined type not defined" Error Message
Dim FileName2 As String
Path = "M:\"
FileName1 = Range("B3")
FileName2 = Format(Range("B4").Value, "mm-dd-yyyy")
AttFile = Path & FileName3 & FileName4 & ".pdf"
ActiveWorkbook.SaveAs Filename:=AttFile, FileFormat:=xlTypePDF, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
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 = "Hi," & vbNewLine & vbNewLine & "Please process the attached Equity ETL." & vbNewLine & vbNewLine & "Thank you,"
myAttachments.Add AttFile
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
Windows("Updated equity transaction request form.xlsm").Activate
End Sub