Solved

VBS/Email

Posted on 2014-02-26
13
597 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 54

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 54

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
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!

 
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
 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
Read this checklist to learn more about the 15 things you should never include in an email signature.
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

740 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