VBA to Generate Email

NIKHAN

New Member
Joined
May 8, 2019
Messages
4
Hello,

I am getting he following error when trying to run this macro. Can someone help me correct it.
"Compile error User-defined type not defined"

The purpose of the vba is to email the latest file from a shared location

Option Explicit

Sub GenerateEmail()
Dim objOutLook As Object
Dim fso As Object
Dim strFile As String
Dim fsoFile
Dim fsoFldr
Dim dtNew As Date, sNew As String
Dim newOutlookInstance As Boolean

Set fso = CreateObject("Scripting.FileSystemObject")
If GetOutlook(objOutLook, newOutlookInstance) Then
strFile = "Z:\Derivative Valuations\Valuation Uploads" 'path to folder
Set fsoFldr = fso.GetFolder(strFile)
dtNew = Now() - TimeValue("01:00:00") '1 hr ago

For Each fsoFile In fsoFldr.Files
If fsoFile.DateCreated > dtNew Then
sNew = fsoFile.Path
With objOutLook.CreateItem(olMailItem)
.To = "test@gmail.om"
.Subject = "Valuations Upload - COB " & Format(DateAdd("d", -1, Date), "dd-mm-yy")
.Body = "Please be advised the following position are missing counterparty prices"
.BodyFormat = olFormatPlain
.Attachments.Add sNew
'.Importance = olImportanceHigh
.Display
End With
End If
Next
If newOutlookInstance Then objOutLook.Quit
Set objOutLook = Nothing

Else
MsgBox "Sorry: couldn't get a valid Outlook instance running"
End If
End Sub


Function GetOutlook(objOutLook As Object, newOutlookInstance As Boolean) As Boolean
End Function
Set objOutLook = GetObject(, "Outlook.Application")
If objOutLook Is Nothing Then
Set objOutLook = New Outlook.Application
newOutlookInstance = True
End If
GetOutlook = Not objOutLook Is Nothing
End Function
End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
.
First ... to comply with the FORUM rules, you should place your posted code between the hash symbols. The result is shown below with the code I've posted.

Rather than reconstruct your code, if you are not opposed to using something that is 'user ready', see below. You will need to edit the paths and perhaps
a few other things. This code looks in the listed path / folder and selects the newest file for attachment.

Code:
Option Explicit


Sub EmailLatestFileInFolder()


    'Declare the variables
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    
    'Specify the path to the folder
    MyPath = "C:\Users\My\Downloads\"
    
    'Make sure that the path ends in a backslash
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    'Get the first Excel file from the folder
    MyFile = Dir(MyPath & "*.xl*", vbNormal)
    
    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    
    'Loop through each Excel file in the folder
    Do While Len(MyFile) > 0
    
        'Assign the date/time of the current file to a variable
        LMD = FileDateTime(MyPath & MyFile)
        
        'If the date/time of the current file is greater than the latest
        'recorded date, assign its filename and date/time to variables
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        
        'Get the next Excel file from the folder
        MyFile = Dir
        
    Loop
  
Dim OApp As Object
Dim OMail As Object
Dim signature As String


Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)


    With OMail
        .Display
    End With
    
signature = OMail.body


    With OMail
    .To = "Who It May Concern@email.com"
        .Subject = "Please See Attached Document"
        .Attachments.Add MyPath & LatestFile
        .body = "FYI" & vbCrLf & vbCrLf & "signature"
        .Display
    End With


On Error GoTo 0


Set OMail = Nothing
Set OApp = Nothing
        
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,655
Messages
6,173,616
Members
452,525
Latest member
DPOLKADOT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top