excel macro for sending emails

I have attached an excel sheet which i wanted to write macro as an attachment. .

when I run a macro an email has to be sent to each persons listed in column A
with Voucher # in the subject line and URL in the body of the message
after the URL, the body of the message will remain same for all the emails:
so I have typed the body of the message in 2nd sheet:

can some one please help me i checked some codes in http://www.rondebruin.nl/sendmail.htm
but im not able to make it which code n how to use..

can some help....

Hello aravindhan_31,

Using hyperlinks will launch the default email program. I think you want to automate this process, yes? There are obstacles with sending email regardless of the method. Most are due to enhanced security features. So, even using CDO isn't a perfect solution either (try using CDO with gmail for some real fun!). If you aren't sending attachments then using Outlook Express to send an email is good choice. Outlook can do a lot more, but the security features get to be annoying. I can code an email macro for you based on your worksheet layout.

Sincerely,
Leith Ross

I dont send any attachments..
I need an automation where emails has to be sent from excel, with attaching 2nd sheet as a body of the message.
and the criteria which i mentioned on my earlier post.

each row has 3 values
email address, Voucher # & URL
in each email voucher # shud be in the subject line and URL in the body of the message with the data in 2nd sheet.

Thanks for your help...in advance....

I am using Outlook 2003.

It would be great if you could help to make this automation.

Thanks in advance....

Hello aravindhan_31,

The following macro module has been installed in the attached workbook. This will send the message body worksheet over in HTML to preserve the format and allow for more than 255 characters in the message body. An annoying problem of launching Outlook is once you "Quit" Outlook the instance you created still runs in the background as an independent program. After running your macro multiple times you, you have multiple instances of Outlook running. This macro takes care of that problem. Each instance the macro creates, it destroys. If Outlook is already open, the macro connects to it, but doesn't close and destroy this instance.

Email Worksheet List with HTML Body

Please Login or Register  to view this content.

Hi,
thanks for your help, I am not able to run the macro.. I am getting an error message as

"Compile Error" Object required:

I have attached a screen short of the error message...


thanks...

Hello ,

I want to apologize to you and others who have viewed my previous code. There were still some problems with the last post. Problems that showed up after more exhaustive testing. This code in this post works correctly. Again, my apologizes for posting bad code.

Send Outlook Emails from Worksheet in HTML format

Please Login or Register  to view this content.

Sincerely,
Leith Ross

hi,
thank you so much for your help...
the program is working good. first I select the sheet and ran the "Hyset" macro.
I created Myemail.htm in my "C drive" as per the code.
then I ran the macro to send emails. all the emails are going good. I sent one email to my email id. I recd the email , But If i click the URL, www.yahoo.com the yahoo page has to open. but Instead, an email box is opening with the id "[email protected]".

could you please enter your email address on the excel sheet and try if you get the same problem. if not,
please let me know If I want to make any changes on my system, or the way to run the program.

note: Actually I will enter some image links on the excel sheet where I have the list of URLs. when I send email, the person who recieves has to seethat image and replies me back.

I sorry forgot to attach the file....


Arvind

Hello aravindhan_31,

My email address was saved in cell "A1" on the "Body of the Message" worksheet. The program should have overwritten it. Just delete the hyperlink and save your workbook. I entered my email and ran the macro. When I clicked on Yahoo, it opened fine.

Sincerely,
Leith Ross

Hi,

Thanks for your help, I still face problem.
I deleted the hyperlink in the 2nd sheet.

Then I ran the macro
for the first person first URL is displaying and when we click that page is opening eg(www.yahoo.com)
but for the 2nd email. URL is displaying www.mrexcel.com but when we click yahoo page is opening..

I think somewhere I am making mistake...

I would be greatful if you could tel me step by step procedure to run the program from the beginning.

Sorry for the inconvenience,

Hello aravindhan_31,

When I run the macro, I don't run Hyset. I am not really sure why you have this routine as Excel will automatically convert a cell entry to a hyperlink if it is either an email address or web address.

I have run this several times, just by clicking the Send Email button. The links work correctly when clicked in the email. That is if it says Yahoo, it takes me to Yahoo. If it says Google, it takes me to Google. I am attaching a copy of the workbook I used for you to try, since I know this copy works correctly.

Sincerely,
Leith Ross

Hi,

weblinks are not working in outlook.emails with URL which are going to any email ID which has configured in outlook are displayed as text not as web links.

could not find out why....


Arvind

Hello Aravindhan,

What you are experiencing is mostly the Service Pack 2 antiphising security protection feature in Outlook 2003. This feature renders Junk emails as plain text and converts HTML links in regular email to plain text. You should be seeing a note in the Infobar to click to enable hyperlinks.

To permanently enable all links, go to Tools > Preferences > Junk Email options and remove the check from the option to Disable Links. However, doing so can put your computer a risk.

Sincerely,
Leith Ross

Hi Thanks for your help Ross,

But I changed that option on my outlook, but still i have the same problem.
Even if that works on my system, I need to inform all my clients to change the setting on their system.

Is there any other way to rectify this problem.

Thanks once again for your help...

hello Ross,

I changed the code, and its working .

from
Wks.Cells(1, "A") = Cells(R, "C").Text
to

Wks.Hyperlinks.Add Anchor:=Wks.Cells(1, "A"), Address:= _
Cells(R, "C").Text, _
TextToDisplay:=Cells(R, "C").Text

Thanks for your all the help.
that program is working fine now.

problem: for each email I need to click Yes to send email. is there any may to avoid this?

Hi,

I have the following code to send automatic email to n number of people at one short.

I have 3 columns in excel.
Column A - Email addres
Column B - Some numbers ( subject)
Column C - Some Links ( body of the message)

wen i run the macro, email goes to all the email address in column A with the subject in column B and with the links in column c respectively and adds the body of the message in sheet 2.

the program is working fine. I just want to add the code to attach an excel file to all these emails.
Assume I have the excel file in My C drive my documents folder.

The code is below


Thanks to leith for providing this code earlier.

'Written: March 31, 2008
'Author: Leith Ross
'Summary: Sends out emails from a worksheet list. The message body is on
' a separate worksheet. This worksheet is copied to a file in
' HTML format. The file is opened an copied as a string which
' becomes the mesaage body in Outlook.

'Used to find the Outlook icon in the system tray. If present then Outlook is running
Private Declare Function FindWindow _
Lib "user32.dll" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Sub EmailFromWorksheet()

Dim FSO As Object
Dim HTMLcode As String
Dim HTMLfile As Object
Dim Msg As String
Dim myInstance As Boolean
Dim olApp As Object
Dim olEmail As Object
Dim olPID As Long
Dim R As Long
Dim TempFile As String
Dim Wks As Worksheet

'Outlook constants aren't available using late binding
Const olByValue = 1
Const olCC = 2
Const olFolderContacts = 10
Const olMailItem = 0
Const olFormatHTML = 2

'Starting Row of Email Data
R = 2

'Set some program variables
TempFile = "C:\MyEmail.htm"
Set Wks = Worksheets("Body of the Message")

'Start Outlook if it isn't running
If FindWindow("Outlook Notification Area Icon Window", vbNullString) = 0 Then
myInstance = True
olPID = Shell("C:\Program Files\Microsoft Office\Office11\OUTLOOK.exe", 2)
End If

'Assign variable to the running instance
Set olApp = GetObject("", "Outlook.Application")

'Trap any errors
On Error GoTo CleanUp

'Stop the email loop if cell is blank
Do While Cells(R, "A") <> ""

'Add URL to the Body of the Message worksheet
Wks.Cells(1, "A") = Cells(R, "C").Text

'Convert the Message worksheet into HTML
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
FileName:=TempFile, _
Sheet:=Wks.Name, _
Source:=Wks.UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read the HTML file back as a string
Set FSO = CreateObject("Scripting.FileSystemObject")
Set HTMLfile = FSO.GetFile(TempFile).OpenAsTextStream(1, -2)
HTMLcode = HTMLfile.ReadAll
HTMLfile.Close
HTMLcode = Replace(HTMLcode, "align=center x:publishsource=", _
"align=left x:publishsource=")

'Compose the email
Set olEmail = olApp.CreateItem(olMailItem)
With olEmail
.To = Cells(R, "A").Text
.Subject = Cells(R, "B").Text 'Voucher Number
.BodyFormat = olFormatHTML
.HTMLBody = HTMLcode
.Send
End With

'Increment Row counter
R = R + 1
'Delete the tempoary file
Kill TempFile
'Delete the Published Object
With ActiveWorkbook.PublishObjects
.Item(.Count).Delete
End With
'Get the next email cell
Loop

CleanUp:
'Close Outlook instance if this macro created it
If myInstance = True Then TerminateProcess olPID
'Was there an error
If Err <> 0 Then
'Delete the Temp File
If Dir(TempFile) <> "" Then Kill TempFile
'Delete the Publish Object
With ActiveWorkbook.PublishObjects
If .Count <> 0 Then .Item(.Count).Delete
End With
End If

'Free memory resources
Set olApp = Nothing
Set olEmail = Nothing
Set FSO = Nothing

End Sub

Public Sub TerminateProcess(ByVal PID As Long)

Dim colProcessList As Object
Dim objProcess As Object
Dim objServices As Object
Dim ProcessRetVal As Long

'Connect to the WMI namespace through the local computer "."
Set objLocator = CreateObject("WbemScripting.SWbemLocator")
Set objServices = objLocator.ConnectServer(".", "root\cimv2")

' Terminate the Application by its Process ID
Set colProcessList = objServices.ExecQuery _
("SELECT * FROM Win32_Process WHERE ProcessId =" & Str(PID))

' WMI requires a loop even if there is only one object
For Each objProcess In colProcessList
ProcessRetVal = objProcess.Terminate()
If ProcessError(ProcessRetVal) Then Exit For
Next objProcess

CleanUp:
Set objLocator = Nothing
Set objServices = Nothing
Set colProcessList = Nothing
Set objProcess = Nothing
End Sub

Private Function ProcessError(ByVal Err_Value As Long) As Boolean
Dim Msg As String
If Err_Value = 0 Then Exit Function

ProcessError:
Select Case Err_Value
Case 2
Msg = "Access Denied"
Case 3
Msg = "Insufficient Privilege"
Case 8
Msg = "Unknown failure"
Case 9
Msg = "Path Not Found"
Case 21
Msg = "Invalid Parameter"
End Select
MsgBox Msg, vbexcalamtion, "WMI Win32_Process"
ProcessError = True
End Function

the same question has been posted in

http://www.mrexcel.com/forum/showthr...23#post1665123