VBS/Email

W.E.B
W.E.B used Ask the Experts™
on
Hello,
can you please help.
I'm using below code to send emails through outlook.
2 Issues I have.

1- I need to attach any file (in the specific  folder) with the name that starts with Sequencing and extension xlsx.

2- I need to view the email before sending.    objEmail.Display

Set objEmail = CreateObject("CDO.Message")

objEmail.From = "test.com@test.com"
objEmail.To = "www@test.com"
objEmail.cc = ""
objEmail.BCC = "www@test.com"
objEmail.Subject = "Files" 
objEmail.Textbody = "***** Please see attached files *****"
objEmail.AddAttachment "C:\Users\Desktop\Test\Sequencing.xlsx"


'Configuration Info
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.test.com"
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "www@test.com"
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 1025
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

objEmail.Display

Open in new window

thank you for any help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Are you doing this as a standalone VBS script, or as an embedded VBA module run from inside Outlook?

~bp

Author

Commented:
Hello,
as a standalone VBS script
thanks
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
With CDO in a standalone VBS script you can't display the message before sending, it isn't supported by the CDO object model.

I guess you could use a basic VBS message Box and display some of the info from the email, and ask for confirmation, but it wouldn't actually look like an email message.

~bp
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

do ALL users of this script have Outlook installed and configured ?

if so then you should be able to use Outlook object rather than CDO.

Author

Commented:
hello,
yes, all users have outlook  installed..

can you help with the object script please.

thanks.
my test shows working on ol2007
'outlook message creation

'ideas from http://support.microsoft.com/kb/161088
const oMailItem = 0
const olTo=1, olCC = 2, olBCC = 3

Dim olApp,  newMail
Dim ToAddress,FromAddress, MessageSubject
dim oRecipient


Set olApp = WScript.CreateObject("Outlook.Application")

Set newMail = olApp.CreateItem(olMailItem)

With newMail
	set oRecipient = .Recipients.Add("www@test.com")
	oRecipient.Type = olTo

	'set oRecipient = .Recipients.Add("www@test.com")
	'oRecipient.Type = olCC

	set oRecipient = .Recipients.Add("www@test.com")
	oRecipient.Type = olBCC

	.Subject = "Files" 
	.Body = "***** Please see attached files *****"
	.HTMLBody = "<p>***** Please see attached files *****</p> for your information"

	'.AddAttachment "C:\Users\Desktop\Test\Sequencing.xlsx"
	.Attachments.Add "C:\ee\inventory_std.txt"
	.Display
End With

set olApp = nothing

Open in new window

Author

Commented:
Hello Rob,
I very much appreciate the help,
but, the code doesn't allow me to choose the email account to send from.
Also,
it doesn't solve my initial issue.

1- I need to attach any file (in the specific  folder) with the name that starts with Sequencing and extension xlsx.

Thank you.
one thing at a time !
1. on my Outlook 2007 it still allows the account selection box.  Do you want to select a particular account ? It assigns the users default account.

2. do you want the first matching file or all ?  assuming more than one may exist...
'outlook message creation

'ideas from http://support.microsoft.com/kb/161088
const oMailItem = 0
const olTo=1, olCC = 2, olBCC = 3

Dim olApp,  newMail
Dim ToAddress,FromAddress, MessageSubject
dim oRecipient

dim folderLoc, filespec, filefound

folderloc = "c:\ee\ee3\"  'change to suit
filespec = "sequencing.*\.xlsx"   'a regular expression to match 'sequencing*.xlsx'

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(folderloc)
Set colFiles = objFolder.Files 
set re = new RegExp
re.Pattern = filespec
re.IgnoreCase = true

Set olApp = WScript.CreateObject("Outlook.Application")

Set newMail = olApp.CreateItem(olMailItem)

With newMail
	set oRecipient = .Recipients.Add("www@test.com")
	oRecipient.Type = olTo

	'set oRecipient = .Recipients.Add("www@test.com")
	'oRecipient.Type = olCC

	set oRecipient = .Recipients.Add("www@test.com")
	oRecipient.Type = olBCC

	.Subject = "Files" 
	.Body = "***** Please see attached files *****"
	.HTMLBody = "<p>***** Please see attached files *****</p> for your information"

	for each filefound in colFiles
		'wscript.echo ">" & filefound & "<"
		if re.Test(filefound.Name) then
			.Attachments.Add folderloc & filefound.name
		end if
    next
	
	.Display
End With

set olApp = nothing

Open in new window

Capture.PNG

Author

Commented:
Hi Rob,
thank you for your help.

this script will be used by few people, I want all of them to be using the same email account to send from.

user             wassim@wassim.com
pass            password
smtp           mail.wassim.com
smtp port   1025

thanks again,
Ill check but  I believe each user has to set up thst account on their outlook.
What you eant is better suited to server side mail sending but then u lose the ability to view and review the mage before it is sent.
cant find a definitive answer

this seems to work and opens message but I havent tested what the end result is.
	
      senderaddress = "example@example.com"
      .SentOnBehalfOfName = senderaddress
	'.Sender = senderaddress
	.ReplyRecipients.Add senderaddress
	.Subject = "Files" 

Open in new window

but there appear to be some issues... see http://msdn.microsoft.com/en-us/library/office/aa210946(v=office.11).aspx

particularly the restrictions...SenderName is readonly... Outlook blocks code that attempts to access the SenderName property for security reasons.  .

then http://social.msdn.microsoft.com/Forums/office/en-US/fc135d90-edde-4d3e-85f7-accf77278fd9/creating-outlook-email-in-access-vba-with-send-of-behalf-of-permission?forum=accessdev  indocates that you can use the SendOnBehalf but will still have the individual users name, though replies go to the correct address I believe.


expression.SentOnBehalfOfName  is read/write.

also need to set the sendon behalf property for each user.. http://anandthearchitect.com/2011/07/17/exchange-2010-you-cant-send-a-message-on-behalf-of-this-user-unless-you-have-permission-to-do-so/

Author

Commented:
Thanks for all your help Rob,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial