Solved

VBS/Email

Posted on 2014-02-26
13
576 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:W.E.B
  • 5
  • 5
  • 2
13 Comments
 
LVL 52

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:W.E.B
ID: 39888970
Hello,
as a standalone VBS script
thanks
0
 
LVL 52

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:W.E.B
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:W.E.B
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:W.E.B
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:W.E.B
ID: 39904791
Thanks for all your help Rob,
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
If you don't know how to downgrade, my instructions below should be helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

919 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

13 Experts available now in Live!

Get 1:1 Help Now