-
Jun 25th, 2020, 06:35 AM
#1
Thread Starter
New Member
Regarding sending email using VBA
I am learning and very new into this field, so I do not have full understanding on everything...but I would like to ask a couple of questions to whom may concern.
1. When I'm trying to send email using outlook, there will be a pop up regarding there is an attachment in the email, so I have click "ok" every time there is a pop up. And I am trying to get rid of the pop up.
SendKeys "{TAB}{ENTER}", True
I already added this code into data, which I can already send out 1 email without the pop up. However the pop up still occur after the 1st email. Is there anyway to solve this?
2. There are 2 emails in Outlook, one for private use and one for public use, and I would like to use the public email to send out email. However I do not know how to specify on only using only the second public email using VBA. I already looked through a lot of information and I still cannot achieve what I want.
Here is the codes I wrote. I hope anyone who may concern could help me.
Option Explicit
Sub Send_email_fromexcel()
Dim edress As String
Dim subj As String
Dim message As String
Dim filename As String
Dim outlookapp As Object
Dim outlookmailitem As Object
Dim myAttachments As Object
Dim path As String
Dim lastrow As Integer
Dim attachment As String
Dim x As Integer
x = 2
Application.DisplayAlerts = False
Application.SendKeys "TabEnter", False
Application.DisplayAlerts = True
Do While Sheet1.Cells(x, 1) <> ""
Set outlookapp = CreateObject("Outlook.Application")
Set outlookmailitem = outlookapp.createitem(0)
Set myAttachments = outlookmailitem.Attachments
path = "C:\Users\lo\Desktop\123.pdf"
edress = Sheet1.Cells(x, 1)
subj = Sheet1.Cells(x, 2)
attachment = path + filename
outlookmailitem.To = edress
outlookmailitem.cc = ""
outlookmailitem.bcc = ""
outlookmailitem.Subject = subj
outlookmailitem.body = Sheet1.Cells(x, 3)
myAttachments.Add (attachment)
outlookmailitem.display
SendKeys "{TAB}{ENTER}", True
outlookmailitem.send
lastrow = lastrow + 1
edress = ""
x = x + 1
Loop
Set outlookapp = Nothing
Set outlookmailitem = Nothing
End Sub
-
Jun 25th, 2020, 07:33 AM
#2
Re: Regarding sending email using VBA
attachment = path + filename
as far as i can tell filename is an empty variable, so should not be used, anyway path contains the fullpath including filename, also it is much better practice to use & for concatenating strings
Set outlookapp = CreateObject("Outlook.Application")
this line should definitely be before the do loop, as it is you create a new instance of outlook for every email, you should also quit the application at the end of the loop, before setting the object to nothing, else it will remain open but not visible, this may also be to do with why sendkeys only works the first time, though i was surprised it worked at all
question 2.i am sure would be simple, if i understood what was being asked, though on rereading i now presume you are talking about actual email accounts? i will have a look later
edit: see https://www.rondebruin.nl/win/s1/outlook/account.htm
Last edited by westconn1; Jun 25th, 2020 at 07:42 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|