Sending mail from outlook using VBA masro

May 21, 2014
Reaction score

I wrote a outlook macro in VBA to ftp the files and send a notification mail after ftp.

Here is my code, in the code, tha attachment from a particular mail is taken and save in local disk by changing its name, then ftp the attachment files, after ftp, a notification mail has to send. Here the code wont check for ftp success before sending the notification mail.

My doubt is that how can i send mail if ftp is success only and also anther mail if ftp got failure, saying files failed in ftp.

Could u please any one help me to send the notification mail if the ftp got success alone.

Private Sub Application_NewMail()

Dim ns As NameSpace
Dim Inbox As MAPIFolder
Dim Item As Object
Dim DT As Date
Dim Atmt As Attachment
Dim FileName(0 To 10) As String
Dim name_file(0 To 10) As String
Dim i As Integer
Dim n As Integer

Dim j As Integer
Dim moveToFolder As MAPIFolder

Dim FTPServ As String
Dim fNum As Long
Dim path As String
Dim File As String
Dim counts As Long

Dim batFileHandle As Integer

path = "D:\"
FTPServ = " " 'ip address

Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
Set moveToFolder = ns.Folders("(E-Mail- id)").Folders("Inbox").Folders("Processed")
i = 0

If Inbox.Items.count = 0 Then
MsgBox "There are no messages in the Inbox.", vbInformation, _
"Nothing Found"
Exit Sub
End If

For Each Item In Inbox.Items
j = 0
For Each Atmt In Item.Attachments
If Atmt Like "*IREV*" Then
name_file(i) = Atmt.FileName
FileName(i) = Replace(name_file(i), "IREV", "lfsinput.0.IREV")
FileName(i) = "D:\Attachments\" & FileName(i)
Atmt.SaveAsFile FileName(i)
i = i + 1
j = j + 1

End If
Next Atmt

If j > 0 Then
j = 0

'Mounting file command for _ftp.exe

fNum = FreeFile()
Open path & "\FtpComm.txt" For Output As #fNum
Print #fNum, "open iip adress"
Print #fNum, " " 'user mane
Print #fNum, " " 'password
For n = 0 To i - 1
Print #fNum, "put " & FileName(n)
Next n
Print #fNum, "close" ' close connection
Print #fNum, "quit" ' Quit ftp program
Close #fNum

'Create executable

batFileHandle = FreeFile
Open path & "\doFtp.bat" For Output As #batFileHandle
Print #batFileHandle, "ftp -n -i -g -s:"; path & "\ftpComm.txt"
Close #batFileHandle

Shell (path & "\doFtp.bat"), vbMaximizedFocus

'sending mail notification

Set objMail = Application.CreateItem(olMailItem)
objMail.Recipients.Add "<(E-Mail- Id)>"
objMail.Subject = "NOTIFICATION"
objMail.HTMLBody = " Hi All, <br><br>The following Input files for IRDET have been ftp'd in the "oksgpdnb" profile<br>Input Files: " & name_file(0) & " and " & name_file(1)

'Moving FTP'd emails to processed folder

If moveToFolder.DefaultItemType = olMailItem Then
Item.Move moveToFolder
End If

End If

Next Item

If i > 0 Then
MsgBox "I found " & i & " attached files." _
& vbCrLf & "I have saved them into the D:\Attachments folder.", vbInformation, _

'MsgBox "I didn't find any attached files in your mail.", vbInformation, _
End If

End Sub


Aug 18, 2015
Reaction score

Im having trouble getting the Reply To using a template to work.

Currently have the script below - Can anyone help getting this to work?

Thanks in advance

Sub ReplywithTemplate()
Dim Item As Outlook.MailItem
Dim oRespond As Outlook.MailItem

Set oRespond = Application.CreateItemFromTemplate("C:\users\nelsonh\documents\EOI Reply.oft")

With oRespond = Item.Reply
.Subject = "EOI Request"


End With
Set oRespond = Nothing
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question