Hi,
I'm using the code below (snipset) to send a mail with a Excel range in the body from within Excel.
This works almost perfect.
The only problem i'll have (randomly) is that i see that the send message isn't always moved to the destination folder.
To avoid this behavior, i would like to check if the send message appears into the Send Items folder before i move it to the destination folder.
Anyone out there who can provide me those few code lines that i need to place between the .send & moving the message to the destination folder (between the 2 red lines in the sub below)?
I think that a kind of loop will do it, but i have no clue on how to code it. All used code so far in this sub comes from different sites, and put it toghether like a jigsaw .
the criteria to look for is: strSubject & Date (green line).
Code below runs from a Excel 2010 worksheet and use late binding.
This makes it easier to distribute or run the worksheet on different PC's without the need to set everywhere a reference to the Outlook 14 vba library.
Sub Mail_Selection_Range_Outlook_Body()
'basic code from:
'http://www.rondebruin.nl/mail/folder3/mail4.htm
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2010
'
'adapted & extended by Ludo Soete
'--------------------------------'
<SNIP
'create a new folder to store the mails after sending
'folder name = "Verpakte Units"
'if folder doesn't exist, create it
On Error GoTo errorhandler
Set myDestFolder = mySendMail.Folders("Verpakte Units")
'
On Error Resume Next
With OutMail
> To = strMailToRec1 & ";" & strMailToRec2 & ";" & strMailToRec3 & ";" & strMailToRec4 'strRecipient
> CC = ""
> BCC = ""
> Subject = strSubject & Date
> HTMLBody = strBody1 & "<br>" & "<br>" & strBody2 & "<br>" & strFrom & "<br>" & "<br>" & "Verstuurd om: " & Time & "<br>" & "<br>" & RangetoHTML(rng)
.Send '.Display '
'inform the user that the mail has been send
'dont move this, ensure there's enough time to send so that we can move the mail
If blnMsgboxSendToMessage = True Then
MsgBox "Uw mail werdt verstuurd naar :" & vbCrLf & vbCrLf & strMailToRec1 & vbCrLf & strMailToRec2 & vbCrLf & strMailToRec3 & vbCrLf & strMailToRec4, vbInformation, "Mail verstuurd"
End If
'check if message moved to the SendItems folder before moving to destination folder
'
'move send mail to myDestination folder
Set myItem = myItems.Find("[Subject] =" & strSubject & Date) '
While TypeName(myItem) <> "Nothing"
If myItem.Subject = strSubject & Date Then
myItem.Move myDestFolder
End If
Set myItem = myItems.FindNext
Wend
strDestinationFolder = myDestFolder.Name
strSendMailboxName = mySendMail.Name
'show frmMsgMovedMessage depending on the blnfrmShowMovedMessage value
If blnfrmShowMovedMessage = True Then
frmMsgMovedMessage.Show
End If
End With
End Sub
<SNIP
Thanks for any help
regards,
Ludo
I'm using the code below (snipset) to send a mail with a Excel range in the body from within Excel.
This works almost perfect.
The only problem i'll have (randomly) is that i see that the send message isn't always moved to the destination folder.
To avoid this behavior, i would like to check if the send message appears into the Send Items folder before i move it to the destination folder.
Anyone out there who can provide me those few code lines that i need to place between the .send & moving the message to the destination folder (between the 2 red lines in the sub below)?
I think that a kind of loop will do it, but i have no clue on how to code it. All used code so far in this sub comes from different sites, and put it toghether like a jigsaw .
the criteria to look for is: strSubject & Date (green line).
Code below runs from a Excel 2010 worksheet and use late binding.
This makes it easier to distribute or run the worksheet on different PC's without the need to set everywhere a reference to the Outlook 14 vba library.
Sub Mail_Selection_Range_Outlook_Body()
'basic code from:
'http://www.rondebruin.nl/mail/folder3/mail4.htm
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2010
'
'adapted & extended by Ludo Soete
'--------------------------------'
<SNIP
'create a new folder to store the mails after sending
'folder name = "Verpakte Units"
'if folder doesn't exist, create it
On Error GoTo errorhandler
Set myDestFolder = mySendMail.Folders("Verpakte Units")
'
On Error Resume Next
With OutMail
> To = strMailToRec1 & ";" & strMailToRec2 & ";" & strMailToRec3 & ";" & strMailToRec4 'strRecipient
> CC = ""
> BCC = ""
> Subject = strSubject & Date
> HTMLBody = strBody1 & "<br>" & "<br>" & strBody2 & "<br>" & strFrom & "<br>" & "<br>" & "Verstuurd om: " & Time & "<br>" & "<br>" & RangetoHTML(rng)
.Send '.Display '
'inform the user that the mail has been send
'dont move this, ensure there's enough time to send so that we can move the mail
If blnMsgboxSendToMessage = True Then
MsgBox "Uw mail werdt verstuurd naar :" & vbCrLf & vbCrLf & strMailToRec1 & vbCrLf & strMailToRec2 & vbCrLf & strMailToRec3 & vbCrLf & strMailToRec4, vbInformation, "Mail verstuurd"
End If
'check if message moved to the SendItems folder before moving to destination folder
'
'move send mail to myDestination folder
Set myItem = myItems.Find("[Subject] =" & strSubject & Date) '
While TypeName(myItem) <> "Nothing"
If myItem.Subject = strSubject & Date Then
myItem.Move myDestFolder
End If
Set myItem = myItems.FindNext
Wend
strDestinationFolder = myDestFolder.Name
strSendMailboxName = mySendMail.Name
'show frmMsgMovedMessage depending on the blnfrmShowMovedMessage value
If blnfrmShowMovedMessage = True Then
frmMsgMovedMessage.Show
End If
End With
End Sub
<SNIP
Thanks for any help
regards,
Ludo