Results 1 to 2 of 2

Thread: Regarding sending email using VBA

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2020
    Posts
    1

    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

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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
  •  



Click Here to Expand Forum to Full Width