anilkumard
New Member
- Joined
- Nov 18, 2016
- Messages
- 2
what this code do?
this code sends out emails
why this code? why not other code?
well other codes interrupt my other activities or i would say i have to sit idle watching the screen while they take up the screen. the below given code silently does the job without my attention.
what am i trying to do ?
i have a daily task of sending out emails with reports to my people, this code sends only text as the body of the email. As we all know most of the reports consists of rows and columns with numbers in it, so is my report too.
i am unable to put the html code or a part of word document or a part of excel in the body of every email i send out.
i heartily thank you and appreciate the time you have taken to consider this as your challenge.
i hope i will get a solution from this forum members.
Thank you and here is the code i picked up from a website -
this code sends out emails
why this code? why not other code?
well other codes interrupt my other activities or i would say i have to sit idle watching the screen while they take up the screen. the below given code silently does the job without my attention.
what am i trying to do ?
i have a daily task of sending out emails with reports to my people, this code sends only text as the body of the email. As we all know most of the reports consists of rows and columns with numbers in it, so is my report too.
i am unable to put the html code or a part of word document or a part of excel in the body of every email i send out.
i heartily thank you and appreciate the time you have taken to consider this as your challenge.
i hope i will get a solution from this forum members.
Thank you and here is the code i picked up from a website -
Rich (BB code):
Sub OutlookMail_3()
'Automate Sending Emails from Excel, using Outlook. Send multiple mails to ids sourced from the Host Workbook's sheet.
'Automating Outlook from Excel, using Late Binding. You need not add a reference to the Outlook object library in Excel (your host application), in this case you will not be able to use the Outlook's predefined constants and will need to replace them by their numerical values in your code.
'variables declared as Object Type, which can be a reference to any object:
Dim oApplOL As Object
Dim oMiOL As Object
Dim lastRow As Long
Dim ws As Worksheet
Dim strMailSubject As String
Dim strMailMessage As String
'set worksheet:
Set ws = ActiveWorkbook.Sheets("Sheet1")
'Email ids are entered in column A of Sheet5 - determine last data row in column A of the worksheet:
lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
'Create a new instance of the Outlook application, if an existing Outlook object is not available.
'Set the Application object as follows:
On Error Resume Next
Set oApplOL = GetObject(, "Outlook.Application")
'if an instance of an existing Outlook object is not available, an error will occur (Err.Number = 0 means no error):
If Err.Number <> 0 Then Set oApplOL = CreateObject("Outlook.Application") End If
'disable error handling:
On Error GoTo 0
'ignores an invalid mail id syntax, and code execution will resume
On Error Resume Next 'loop through all cells in column A: For i = 1 To lastRow 'validate mail syntax:
'Like operator in vba: If the string satisfies the specified pattern, it will return True. * denotes zero or more characters, ? denotes a single character.
'The following statement returns True if the string has an "@" and atleast one character before it, and atleast one character after it, then followed by "." and atleast one character after it. If Trim(ws.Cells(i, 1).Value) Like "*?@?*.?*" Then 'pick mail subject from Range("C1"): strMailSubject = ws.Cells(1, 3) 'Add text to mail message and also pick text from Range("C2") and Range("C3"): strMailMessage = "Hello " & ws.Cells(i, 2) & vbCrLf & vbCrLf & ws.Cells(3, 3) & vbCrLf & vbCrLf & "Best Regards," & Chr(13) & "Administrator" 'create mail item: 'Built-in constant olMailItem has been replaced by its value 0. Set oMiOL = oApplOL.CreateItem(0) With oMiOL 'pick mails ids from column A:
.To = ws.Cells(i, 1)
'Built-in constant olImportanceLow has been replaced by its value 0.
.Importance = 0
.Subject = strMailSubject
.Body = strMailMessage
.ReadReceiptRequested = False .send End With End If 'set a 2 seconds time interval:
Application.Wait (Now + TimeValue("0:00:02")) Next i 'clear the object variables:
Set oApplOL = Nothing
Set oMiOL = Nothing End Sub
Last edited by a moderator:
