Outlook Microsoft Outlook VBA Macro

Joined
May 21, 2014
Messages
2
Reaction score
0
Hi,

I wrote a outlook macro in VBA to ftp the files and send a notification mail after ftp.
Could u please any one help me to send the notification mail if the ftp got success alone.

Here is my code which will get the files from mail, change the name and save in a path, then ftp the files and send the mail of notification.


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

'Processed
Dim j As Integer
Dim moveToFolder As MAPIFolder

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

'executable
Dim batFileHandle As Integer

path = "D:\SUGANYA"
FTPServ = "204.254.175.102"

Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
Set moveToFolder = ns.Folders("(e-mail address removed)").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:\SUGANYA\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 204.254.175.102"
Print #fNum, "bderet"
Print #fNum, "bderet"
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 "<[email protected]>"
objMail.Recipients.ResolveAll
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)
objMail.Send

'Moving FTP'ed 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:\SUGANYA\Attachments folder.", vbInformation, _
"Finished!"

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

End Sub
 
Hi, You are trying to create a BATCH fle in DOS to do the FTP process. It is difficult to find the return status in this method.
Try to search on how to FTP a file directly in VBA using any available options or WIndows API Functions.
 
Back
Top