PDA

View Full Version : Compile Error: User-defined type not defined


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
Kenneth Hobs
09-07-2017, 12:19 PM
It is a typo? Change Sting to String.
Paul_Hossler
09-07-2017, 12:21 PM
Dim FileName1 As Sting '"User defined type not defined" Error Message



You misspelt 'String'
foxyginger
09-07-2017, 01:03 PM
NEW ISSUE: I changed the code to reflect what I was attempting to do better, and now all of it works great except when I'm trying to attach the newly created PDF to the Outlook email, it can't find the document in my files. What string/file path do I use to attach the PDF with FileName:=Range("B3").Value & " " & Format(Range("B4").Value, "mm-dd-yyyy") & " " & "Wire Information" to the email?

See revised version below:


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 with Kay McConnell as the recipient.


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

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("B3").Value & " " & Format(Range("B4").Value, "mm-dd-yyyy") & " " & "Wire Information", _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False 'THIS IS THE PDF FILE I'M TRYING TO ATTACH TO THE EMAIL BELOW

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 and Wire Information"
.Body = "Hi," & vbNewLine & vbNewLine & "Please process the attached Equity ETL." & vbNewLine & vbNewLine & "Thank you,"
myAttachments.Add ' ERROR MESSAGE -- How do I define the file path to find the newly created PDF [Data Source Needed]
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing


End Sub
Kenneth Hobs
09-07-2017, 01:20 PM
Sub Main()
Dim s As String
s = Range("B3").Value & " " & _
Format(Range("B4").Value, "mm-dd-yyyy") & _
" " & "Wire Information" & ".pdf"
Debug.Print s

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=s, _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False 'THIS IS THE PDF FILE I'M TRYING TO ATTACH TO THE EMAIL BELOW

'....
myAttachments.Add s
'....
End Sub
foxyginger
09-07-2017, 01:32 PM
Still in error somehow.

Run-time error '440':

Cannot add the attachment; no data source was provided


Sub Main()

Dim s As String
s = Range("B3").Value & " " & _
" " & "Wire Information" & ".pdf"
Debug.Print s

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=s, _
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 and Wire Information"
.Body = "Hi Kay," & vbNewLine & vbNewLine & "Please process the attached Equity ETL." & vbNewLine & vbNewLine & "Thank you,"
myAttachments.Add
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing




End Sub




Sub Main()
Dim s As String
s = Range("B3").Value & " " & _
Format(Range("B4").Value, "mm-dd-yyyy") & _
" " & "Wire Information" & ".pdf"
Debug.Print s

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=s, _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False 'THIS IS THE PDF FILE I'M TRYING TO ATTACH TO THE EMAIL BELOW

'....
myAttachments.Add s
'....
End Sub
Kenneth Hobs
09-07-2017, 02:16 PM
You gave no input value for myAttachments.Add.

myAttachments.Add s