excel form email 1

[SQLScholar]

Hey,

I am trying to create a hyperlink on a form, which opens up the persons mail client with my email address in the To: box and possible an item in the subject box. It would then allow them to fill in and send.

Any help much appreciated

TIA

Dan

 

[xlbo]

What email client would be used ?? Rgds
~Geoff~

 

[SQLScholar]

dependant on the system.

What i would require would be similar to the hyperlink in HTML. So that what ever system it opens up a blank email with my email address in the to box, in the default program.

I have notice an HTML add on pack in VBA... is there something i can use in this?? if i did would everyone have to install it to be able to use this link (cause if so this is usless).

Thanx Goeff

 

[xlbo]

The problem with doing it from VBA is that you have to define which program to use - there is very different code for outlook and lotus notes to name but 2. If you don't know which email client is going to be used, I would just go for letting them create the email themselves - just provide your address...it'll be much easier Rgds
~Geoff~

 

[SQLScholar]

i would guess there main program would be outlook. Is there any chance you can help me with the code if we assume outlook?

Cheers

Dan

 

[xlbo]

Plank - here's some code - I just did a search on teh archives for "email from excel" and got loads of hits - this includes attachment properties as well which you may want to get rid of:

Sub sendMessage(Optional AttachmentPath)

Dim olookApp As Outlook.Application
Dim olookMsg As Outlook.MailItem
Dim olookRecipient As Outlook.Recipient
Dim olookAttach As Outlook.Attachment

' create the Outlook session.
Set olookApp = CreateObject("Outlook.Application&quot

' create the message.
Set olookMsg = olookApp.CreateItem(olMailItem)

With olookMsg
' add the To recipient(s) to the message.
Set olookRecipient = .Recipients.Add("Christopher Wyke&quot
olookRecipient.Type = olTo

' add the CC recipient(s) to the message.
Set olookRecipient = .Recipients.Add("Robert Dil&quot
olookRecipient.Type = olCC

' set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "Last test - I promise." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

' add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set olookAttach = .Attachments.Add(AttachmentPath)
End If

' resolve each Recipient's name
For Each olookRecipient In .Recipients
olookRecipient.Resolve
If Not olookRecipient.Resolve Then
olookMsg.Display ' display any names that can't be resolved
End If
Next
.Send

End With
Set olookMsg = Nothing
Set olookApp = Nothing

End Sub Rgds
~Geoff~

 

[SQLScholar]

Thanks mate but i am getting an error.

Sub sendMessage(Optional AttachmentPath)

Dim olookApp As Outlook.Application
Dim olookMsg As Outlook.MailItem
Dim olookRecipient As Outlook.Recipient
Dim olookAttach As Outlook.Attachment

' create the Outlook session.
Set olookApp = CreateObject("Outlook.Application&quot

' create the message.
Set olookMsg = olookApp.CreateItem(olMailItem)

With olookMsg
' add the To recipient(s) to the message.
Set olookRecipient = .Recipients.Add("Christopher Wyke&quot
olookRecipient.Type = olTo

' add the CC recipient(s) to the message.
Set olookRecipient = .Recipients.Add("Robert Dil&quot
olookRecipient.Type = olCC

' set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "Last test - I promise." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

' add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set olookAttach = .Attachments.Add(AttachmentPath)
End If

' resolve each Recipient's name
For Each olookRecipient In .Recipients
olookRecipient.Resolve
If Not olookRecipient.Resolve Then
olookMsg.Display ' display any names that can't be resolved
End If
Next
.Send

End With
Set olookMsg = Nothing
Set olookApp = Nothing

End Sub

The error highlights the above and says "user defined type not defined"

 

[xlbo]

You'll need to set a reference to outlook

In VBE Tools>References, find all outlook references and tick them Rgds
~Geoff~

 

[SQLScholar]

99% there.

Only problem is now is it sends without showing the email. Waht i need is that when they click it a email is opened so they can fill it in. i have tried removing the .send but then it does nothing.

Sorry to be a nescience... but if its any concelation you guys and gals at these forums have created a real intrest in learning VBA (and i am getting there slowly).

Cheers

Dan

 

[SQLScholar]

ooo... i forgot to ask.

Will this work on with people without this extra library installed, becuase if not it isnt much use.

cheers

Dan

 

[xlbo]

Hi - not done much in Outlook but try this:

Sub sendMessage()

Dim olookApp As Outlook.Application
Dim olookMsg As Outlook.MailItem
Dim olookRecipient As Outlook.Recipient
Dim olookAttach As Outlook.Attachment

' create the Outlook session.
Set olookApp = CreateObject("Outlook.Application&quot
olook.app.visible = true
' create the message.
Set olookMsg = olookApp.CreateItem(olMailItem)

With olookMsg
' add the To recipient(s) to the message.
Set olookRecipient = .Recipients.Add("Christopher Wyke&quot
olookRecipient.Type = olTo

' set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Importance = olImportanceHigh 'High importance

' .Send

End With
Set olookMsg = Nothing
Set olookApp = Nothing

End Sub

As to the references.......not sure...depends how the application works...whether it's within 1 company and one file residing on a network (should be ok) or whether it's seperate workbooks / databases / whatever that are sent to different people (porobably gonna fall over) Rgds
~Geoff~

 

[SQLScholar]

Still no luck.

This is getting very frustraiting, i can easily do it by creating a hyperlink to "mailto:myemail@here.com" in excel and that does exactly what i need, but this doesnt really help me trying to do the same from VBA in excel. I have even tried creating a macro which creates a link then runs it, then deletes it - but this causes outlook to open then excel wont respond again.

Well i think i am on a lost cause unless someones got some ideas.

Dan

 

[SQLScholar]

Managed it!!!!

Still dont know if it runs without the library?

here is the code for your information

Public Function SendEmail(Optional msgCC As String, Optional msgSubject As String, Optional msgBody As String, Optional ImportanceHigh As Boolean = False, Optional SendNow As Boolean = False)
    
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
        Set objOutlook = CreateObject("Outlook.Application")
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
        With objOutlookMsg
            .To = "dan@me.com"
            .Subject = "this"
            .Body = "Type Problem Here"
            .Importance = olImportanceHigh
            'Resolving the message removes all invalid e-mail address, this will handle up to 5 invalid email addresses
            .Display (True)
        End With
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
End Function

I modified it from

thread705-160948 will let you know if it solves me problem without the reference

Cheers

Dan

 

[SQLScholar]

Easy answer to this one hopefully (i just cant find it)

How do i make the string msgSubject = the cell A1 9 of sheet called general.

Cheers

Dan ----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[xlbo]

msgSubject = sheets("General&quot.range("A19&quot.text

HTH Rgds
~Geoff~