Log In
Register
Home
December 4, 2021
Offline
I was able to file a simple VBA code that corresponded to my needs. A list with 2 columns, A for the Names (only so users can add to it and manage it), and B a list of email addresses. I have attached my Excel template, with my 2 email addresses for testing. However the test never made it that far. I am gettin g an error message when I run it stating "Compile Error: Variable Not Defined". Attached is a copy of the error message.
I also have a second request too. There is now an outlook template file (.OFT) used to generate this email. Can you add that in so rather than a "blank" email template, the template of "Change Notification.oft" is used? I can adjust the file path to my computer.
.
Sub SendEmailList()
On Error GoTo ErrHandler
' Set Outlook object.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
' Create email object.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(olMailItem)
Dim myDataRng As Range
' We'll now set a range.
Set myDataRng = Range("B1:B10" & Cells(Rows.Count, "B").End(xlUp).Row)
Dim cell As Range
Dim iCnt As Integer ' Its just a counter.
Dim sMail_ids As String ' To store recipients email ids.
' Run a loop to extract email ids from the 2nd column.
For Each cell In myDataRng
If Trim(sMail_ids) = "" Then
sMail_ids = cell.Offset(1, 0).Value
Else
sMail_ids = sMail_ids & vbCrLf & ";" & cell.Offset(1, 0).Value
End If
Next cell
Set myDataRng = Nothing ' Clear the range.
With objEmail
.to = sMail_ids ' Assign all email ids to the property.
.Subject = "This is a test message from Arun Banik"
.Body = "Hi, there. Hope you are doing well."
.Display ' Display outlook message window.
End With
' Clear all objects.
Set objEmail = Nothing: Set objOutlook = Nothing
ErrHandler:
'
End Sub
See Answer
Trusted Members
Moderators
November 1, 2018
Offline
Using a template actually fixes the first error (you hadn't defined olMailItem) too:
Option Explicit
Sub SendEmailList()
On Error GoTo ErrHandler
' Set Outlook object.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
' Create email object.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItemFromTemplate("path to your OFT file here")
Dim myDataRng As Range
' We'll now set a range.
Set myDataRng = Range("B1:B10" & Cells(Rows.Count, "B").End(xlUp).Row)
Dim cell As Range
Dim iCnt As Integer ' Its just a counter.
Dim sMail_ids As String ' To store recipients email ids.
' Run a loop to extract email ids from the 2nd column.
For Each cell In myDataRng
If Trim(sMail_ids) = "" Then
sMail_ids = cell.Offset(1, 0).Value
Else
sMail_ids = sMail_ids & vbCrLf & ";" & cell.Offset(1, 0).Value
End If
Next cell
Set myDataRng = Nothing ' Clear the range.
With objEmail
.to = sMail_ids ' Assign all email ids to the property.
.Subject = "This is a test message from Arun Banik"
.Body = "Hi, there. Hope you are doing well."
.Display ' Display outlook message window.
End With
' Clear all objects.
Set objEmail = Nothing: Set objOutlook = Nothing
ErrHandler:
'
End Sub
December 4, 2021
Offline
Velouria,
That works perfect, thanks so much. I have a second very similar project that would contain one mass list
Column A - Name
Column B - Email address
Those are the same as previous
Trigger would be Column F. Does it contain "x" (NOT case sensitive") If "x" is listed, generate email to address in column B . If Column F is blank or contains any other text, then do nothing. Also it is likely this list will exceed 500 (Outlook's max), so a loop must be incorporated to allow multiple emails to auto-generate until all names with "X" in column F have been generated.
This macro will be duplicated for columns G, H & I as triggers too (separate buttons for user to click). An d because of the list being greater than the Outlook max (it will probably be about 3K total email addresses within the company). That is why the loop must be created.
And can it "send" so the user does not have to click the send button? And finally, can it remove the signature so that is not sent?
Trusted Members
Moderators
November 1, 2018
Offline
Is the signature part of the template?
Do you need the code to send one email per person, or simply start a new email for each 500 (or so) addresses?
December 4, 2021
Offline
@Velouria,
No the signature is NOT part of the template. But it gets added when MY email is used. I will do a test with a user tomorrow for one of her Shared Inboxes to see if the signature shows. It would be 1 email for 500 people, than the loop would send another email to the next 500. I just found out there is another criteria (of course). Now although I do not have access to Shared Inboxes, both my users needing this macro do. Now this could be done one of 2 ways:
1) A drop down menu to select the correct Shared Inbox to use
2) It could be "hard coded" into the VBA (just comment out this portion please so I can personalize it as needed)
If it is hard coded, I will clone the macro for each of the different shared email addresses. But that won't matter, as the template will ALSO vary.
Trusted Members
Moderators
November 1, 2018
Offline
This is getting close to consultancy work (which I don't do any more) rather than forum Q&A...
I can't test this as I don't use shared mailboxes, but I think something like this should at least get you started:
Sub SendEmailList()
On Error GoTo ErrHandler
' Set Outlook object.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
Dim myDataRng As Range
' We'll now set a range.
Set myDataRng = Range("B1:B10" & Cells(Rows.Count, "B").End(xlUp).Row)
Dim cell As Range
Dim iCnt As Integer ' Its just a counter.
Dim sMail_ids As String ' To store recipients email ids.
' Run a loop to extract email ids from the 2nd column.
For Each cell In myDataRng
If Len(cell.Value) <> 0 Then
If Trim(sMail_ids) = "" Then
sMail_ids = cell.Offset(1, 0).Value
Else
sMail_ids = sMail_ids & vbCrLf & ";" & cell.Offset(1, 0).Value
End If
iCnt = iCnt + 1
If iCnt = 500 Then
SendEmail objOutlook, sMail_ids
sMail_ids = vbNullString
iCnt = 0
End If
End If
Next cell
ErrHandler:
'
End Sub
Sub SendEmail(objOutlook As Object, ListOfAddresses As String)
' Create email object.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItemFromTemplate("path to your OFT file here")
Dim MailAccount As Object
Set MailAccount = objOutlook.session.acccounts("account display name here")
With objEmail
Set .SendUsingAccount = MailAccount
.to = ListOfAddresses ' Assign all email ids to the property.
.Subject = "This is a test message from Arun Banik"
.Body = "Hi, there. Hope you are doing well."
' .Display ' Display outlook message window.
.Send ' Send the email
End With
End Sub
Answers Post
The following users say thank you to Velouria for this useful post:
Philip TreacyDecember 4, 2021
Offline
Velouria,
Thanks so much. It was not my intention, and I feel so bad that I kept going back for more. But for every inch you had helped me provide to my boss he wanted another yard. Please accept my apology. That was my problem to deal with, and I should not have requested so much assistance from you as the game change. Thanks so very much for all your time.
Trusted Members
October 17, 2018
Offline
Sorry for budding in, but this forum (and (two) other ones I checkout, help wher I can out or post questions) is exactly for that. You can ask, anyone can pick it up and help where he/she can.
Knowledge is something we should always share and it's like programming, when you receive an answer that works you always realize there's a what-if and you want to go one more step.
If you do not ask a question when seeking a result, you will never get an answer, imho, don't apologize
December 4, 2021
Offline
@Hans,
Thanks so much. I really felt bad, and I felt so much better after your post.
Trusted Members
Moderators
November 1, 2018
Offline
Apologies - it was not my intention to make you feel bad. I just wanted to point out that there are generally limits to what you'll get from a forum.
1 Guest(s)
