Email button

[eliozo]

Hello! i need a button where it will ask me to specify a cell. For example, i specified cell A1. Therefore automatically it will open the outlook and prepare an email with the content of A1, B1, C1, D1as per below. (3 other cells beside the one selected).
ps: ignore AW30 and AW31
Thank you!!

Private Sub CommandButton196_Click()

Dim objOutlook As Object

Dim objNameSpace As Object

Dim objOlAccount As Object

Dim objMailItem As Object

Dim Recip As String

Dim Send As String

Dim rng As Range



On Error Resume Next

Set objOutlook = GetObject(, "Outlook.Application")

On Error GoTo 0



If objOutlook Is Nothing Then

Set objOutlook = CreateObject("Outlook.Application")

Set objNameSpace = objOutlook.GetNamespace("MAPI")

End If



Recip = "URGENT REMINDER! " & "Request # " & [A1].Value & " " & "Unit: " & [B1].Value & " " & "Description: " & [C1].Value & " " & "Request Date: " & [D1].Value & " Thank You!"

Send = [AW30].Value

SendCC = [AW31].Value



Set objMailItem = objOutlook.createitem(0)



With objMailItem

.To = Send

.CC = SendCC

.BCC = ""

.Subject = "Urgent Maintenance Request"

.Body = Recip



.display

End With



Set objMailItem = Nothing

Set objOlAccount = Nothing

Set objNameSpace = Nothing

Set objOutlook = Nothing

End Sub

 

[eliozo]

Anyone ?! ?

 

[Trevor G]

Try this out. I have set the reference to use Outlook. I have tried and tested and it works in my testing area.

Sub sendmail()
'Set references to Outlook
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)
Dim inP As String
'Use input box to select the cell
inP = InputBox("Which cell do you want to use", "Test")
Range(inP).Select
'Generate Email
With olMail
    .To = "Test@gmail.com"
    .CC = "Someone@gmail.com"
    .Subject = "Test Email"
    .Body = "Urgent attention " & ActiveCell.Value & " " & ActiveCell.Offset(0, 1).Value & " " & ActiveCell.Offset(0, 2).Value
    .Display 'Use .Display or change to .Send once tested

End With
Set olApp = Nothing

End Sub

 

[eliozo]

Try this out. I have set the reference to use Outlook. I have tried and tested and it works in my testing area.

Sub sendmail()
'Set references to Outlook
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)
Dim inP As String
'Use input box to select the cell
inP = InputBox("Which cell do you want to use", "Test")
Range(inP).Select
'Generate Email
With olMail
    .To = "Test@gmail.com"
    .CC = "Someone@gmail.com"
    .Subject = "Test Email"
    .Body = "Urgent attention " & ActiveCell.Value & " " & ActiveCell.Offset(0, 1).Value & " " & ActiveCell.Offset(0, 2).Value
    .Display 'Use .Display or change to .Send once tested

End With
Set olApp = Nothing

End Sub

Thank you but its giving me an compile error message: user-defined type not defined
And it is pointing to olApp As Outlook.Application

 

[Trevor G]

You have to set the reference to use Outlook. In VBA got o the Tools Menu and then References, search down the list for Microsoft.Outlook XX and select the tickbox then it will work.

 

[eliozo]

Thank you ! Is it possible not to write the cell i want to use but click on it ?!

 

[Trevor G]

You will need to create a UserForm in the VBA screen and from the toolbox add in a control called RefEdit this will allow you to select a cell. You would have to assign the code to a command button on the form, name the control inP change part of the code from the InputBox to the RefEdit control.

That wasn't part of your original question though.

Line to change

inP = InputBox("Which cell do you want to use", "Test")

to

inP = inP.Text

 

[eliozo]

can you please clarify your reply ? I did not get what i should do. I prefer in the vba code to include application.inputbox in order to be able to select a cell directly. Sorry for not clarifying this in the beginning.

 

[Trevor G]

Ref edit is a control you add to a userform in the VBA screen this will allow you to select cells in worksheet and then it can run the rest of the code. To see it in action watch something like this on YouTube.