VBA: Send email to outlook from Excel

[crow216]

Is there a way to have excel send an email to outlook that has a poll and when a person answers the poll, the results return back to excel?

 

[Smitty]

Welcome to the Board!

You can use the Survey tool in the Excel Web App.

Creating surveys with the Excel Web App in Office 365 for education - Education - Site Home - MSDN Blogs

HTH,

 

[crow216]

Welcome to the Board!

You can use the Survey tool in the Excel Web App.

Creating surveys with the Excel Web App in Office 365 for education - Education - Site Home - MSDN Blogs

HTH,

thank you!

This is for something to use in my office and we are on Office 07 so I don't think the solution above works. Ideally, I want to be able to program w/ VBA in excel that when someone clicks a cell or button, it generates an email with a poll. Once that person responds to the poll, I'd like the excel file to read it.

Really appreciate the help!

 

[Smitty]

The Excel Web App is online and free. Once you build the survey you simply send out a url link, then the results are tabulated in an online Excel file that you can download. All you need is a OneDrive account.

 

[REUBEN]

Hi There,

I have developed the below code with a friends help and help available on line. The code is working well, but i have two different mailboxes I use in Outlook, and I want to select only one specific email ID to send these emails from. How do I do that?

Sub Mail_small_Text_Outlook(names() As String, mailAddrs() As String)
For i = 0 To UBound(names, 1) - 1
    Dim intro As String
    Dim message As String
    Dim subject As String
    
    Dim firstName() As String
    firstName = Split(names(i), ",")
    
    intro = "Dear " + firstName(1) + "," + vbNewLine + vbNewLine
    subject = "Your Biometrics test is due"
    'message = "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"


    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
    With OutMail
        .To = mailAddrs(i)
'        .CC = "immigration@relocate.dk"
        .BCC = ""
        .subject = subject
        .Body = intro + GetBoiler("C:\Users\reuben@relocate.dk\Desktop\Biometrics Email.txt")
        '.HTMLBody = intro + GetBoiler("C:\Users\reuben@relocate.dk\Desktop\Biometrics appointment due.htm") & .HTMLBody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
    
    
    Next i


End Sub


Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

 

[REUBEN]

Hi All,

This process has been explained on this page (a big thanks to Ron de Bruin).

I have tried incorporating the concept. checked the first code in Outlook to see which account I wanted to run my code for and it showed it to be account number 2.

Now having tweaked my code accordingly and including the line on (.SentOnBehalfOfName = """SenderName"" <reply@address.com< a="">>"), my code is still sending the message from the 1st or the primary outlook account.

Could anybody help correct this please?

Sub Mail_small_Text_Outlook(names() As String, mailAddrs() As String)
For I = 0 To UBound(names, 1) - 1
    
    Dim intro As String
    Dim message As String
    Dim subject As String
    
    Dim firstName() As String
    firstName = Split(names(I), ",")
    
    intro = "Dear " + firstName(1) + "," + vbNewLine + vbNewLine
    subject = "Your Biometrics test is due"
    
    Dim OutApp As Object
    Dim OutMail As Object
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)


    On Error Resume Next
    With OutMail
        .To = mailAddrs(I)
'        .CC = "immigration@relocate.dk"
        .BCC = ""
        .subject = subject
        .Body = intro + GetBoiler("C:\Users\reuben@relocate.dk\Desktop\Biometrics Email.txt")
        
        .SendUsingAccount = OutApp.Session.Accounts.Item(2)
        
        .Send   'or use .Display
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
    
    Next I


End Sub

</reply@address.com<>

 

[VoG]

See Use the mail account you want in your mail macro

 

[REUBEN]

Thank you VoG. That's the same page's link I had shared in my post too.

I have followed his steps and used the account number 2 in my code. But there seems to be something going wrong.

Firstly my code is part of a form and is being called while in a process. So the sub's heading is different from what's shown on link.

Also when i use Dim OutApp As Outlook.Application and Dim OutMail As Outlook.MailItem. I get a compile error and User defined type not defined. So I went back to using Dim OutApp As Object and Dim OutMail As Object. which runs fine but sends the email from the primary account.

is that creating any issues?

Thank you.