Solved

VBS/Email

Posted on 2014-02-26
13
572 Views
Last Modified: 2014-03-04
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
Comment
Question by:Wass_QA
  • 5
  • 5
  • 2
13 Comments
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39888951
Are you doing this as a standalone VBS script, or as an embedded VBA module run from inside Outlook?

~bp
0
 

Author Comment

by:Wass_QA
ID: 39888970
Hello,
as a standalone VBS script
thanks
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39889414
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39894444
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
 

Author Comment

by:Wass_QA
ID: 39894782
hello,
yes, all users have outlook  installed..

can you help with the object script please.

thanks.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39896278
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Wass_QA
ID: 39898523
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39900164
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
 

Author Comment

by:Wass_QA
ID: 39900226
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39901639
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
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 400 total points
ID: 39902869
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
 

Author Closing Comment

by:Wass_QA
ID: 39904791
Thanks for all your help Rob,
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Create high volume marketing opportunities using email signatures with these top 10 DOs and DON'Ts of email signature marketing.
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now