Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 640
  • Last Modified:

VBS/Email

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.
0
W.E.B
Asked:
W.E.B
  • 5
  • 5
  • 2
1 Solution
 
Bill PrewCommented:
Are you doing this as a standalone VBS script, or as an embedded VBA module run from inside Outlook?

~bp
0
 
W.E.BAuthor Commented:
Hello,
as a standalone VBS script
thanks
0
 
Bill PrewCommented:
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
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Robberbaron (robr)Commented:
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.
0
 
W.E.BAuthor Commented:
hello,
yes, all users have outlook  installed..

can you help with the object script please.

thanks.
0
 
Robberbaron (robr)Commented:
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

0
 
W.E.BAuthor 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.
0
 
Robberbaron (robr)Commented:
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
0
 
W.E.BAuthor 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,
0
 
Robberbaron (robr)Commented:
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.
0
 
Robberbaron (robr)Commented:
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/
0
 
W.E.BAuthor Commented:
Thanks for all your help Rob,
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now