Mixing
Excel and Outlook VBA: Receive message prompt before sending mail in Outlook, based on data in Excel file
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I am completely new to using VBA and have been searching and using the trial-and-error-method to develop an idea I had to help me and my colleagues. But as I now no longer find online pages to help me further, I’ll just start one myself and hope you’ll be able to help me out!
Context: We mainly work with outsourcing and freelancers, and it is sometimes difficult to keep track of who is unavailable and until when. That’s why I’ve pieced together an Excel file in which a userform simplifies entering dates on which people (entered through their email address) are unavailable in the Excel worksheet. That was my first VBA-experience.
Now, the next step is the one which I’m struggling to find a solution for. I’d like to “connect” this Excel file to Outlook, so that when I click “Send” or enter an e-mail address, a macro:
- checks if the entered e-mail address is in the worksheet of the Excel file
- checks if the present moment falls between the dates of unavailability next to that name
- gives a prompt message when both conditions are met to tell me this person is unavailable, and let me cancel sending the email.
- optional: the message prompt has fields which are filled with the according period of unavailability for that person
Would it be possible to connect Excel to Outlook in this way, so that we can continue using Outlook to write our mails? If it is, some help or a link to an already existing guide would be amazing, as I have failed to find anything viable.
Simplified, it should look something like this:
Click “Send” in Outlook email window
Before sending, call Excel file (does not need to be visible)
Check e-mail address column (column A)
Matches “To” field in Outlook email window?
If No, Send email
If Yes, Check “From” date column (column C) next to corresponding email address
Present date is equal to or later than “From” date?
If No, Send Email
If Yes, Check “Until” Date column (column D): present date is earlier than or equal to present date?
If Yes, message prompt: “Name (column B) is not available from “From” until “Until”. Do you still want to send the e-mail?
Buttons:
Yes: Send Email
No: Close prompt, do not send email, but keep email open.
If No, Send Email
Additionnally here's my code for the userform in the Excel file:
Private Sub CommandDate1_Click()
DatePicker1.Show
AbsencePlannerUserForm.StartTextBox.SetFocus
End Sub
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub ClearButton_Click()
Call AbsencePlannerUserForm_Initialize
End Sub
Private Sub CommandDate2_Click()
DatePicker2.Show
AbsencePlannerUserForm.EndTextBox.SetFocus
End Sub
Private Sub EndTextBox_Change()
End Sub
Private Sub ExtraInfoTextBox_Change()
End Sub
Private Sub OKButton_Click()
Dim M_Date As Date
Dim M_Item As String
M_Date1 = StartTextBox
M_Date2 = EndTextBox
M_Item = EmailTextBox
M_Info = ExtraInfoTextBox
Application.ScreenUpdating = False
LastRow = Abwesenheit1.Cells(Rows.Count, "D").End(xlUp).Row
For rw = 2 To LastRow
If Abwesenheit1.Cells(rw, "A") = M_Item And Cells(rw, "C") = M_Date1
And Cells(rw, "D") = M_Date2 And Cells(rw, "E") = M_Info Then GoTo Passem
Next rw
GoTo NO_Dups
Passem:
Application.ScreenUpdating = True
MsgBox "Der Urlaub für " & M_Item & " vom " & M_Date1 & " bis zum " & M_Date2 & " ist schon eingetragen."
Exit Sub
NO_Dups:
Dim emptyRow As Long
'Make Abwesenheit1 active
Abwesenheit1.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = EmailTextBox.Value
Cells(emptyRow, 3).Value = StartTextBox.Value
Cells(emptyRow, 4).Value = EndTextBox.Value
Cells(emptyRow, 5).Value = ExtraInfoTextBox.Value
Application.Visible = True
Me.Hide
End Sub
Private Sub AbsencePlannerUserForm_Click()
End Sub
Private Sub AbsencePlannerUserForm_Initialize()
'Empty EmailTextBox
EmailTextBox.Value = ""
'Empty StartTextBox
StartTextBox.Value = ""
'Empty EndTextBox
EndTextBox.Value = ""
'Empty ExtraInfoTextBox
ExtraInfoTextBox.Value = ""
'Set Focus on EmailTextBox
EmailTextBox.SetFocus
End Sub
Sub open_form()
Application.Visible = False
UserForm1.Show vbModeless
End Sub
Private Sub StartTextBox_Change()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Application.Visible = True
Me.Hide
End Sub
Looking forward to your more experienced input!
excel vba email outlook prompt
asked Jan 3 at 12:23
GuillaumeGuillaume
11
add a comment |
I am completely new to using VBA and have been searching and using the trial-and-error-method to develop an idea I had to help me and my colleagues. But as I now no longer find online pages to help me further, I’ll just start one myself and hope you’ll be able to help me out!
Context: We mainly work with outsourcing and freelancers, and it is sometimes difficult to keep track of who is unavailable and until when. That’s why I’ve pieced together an Excel file in which a userform simplifies entering dates on which people (entered through their email address) are unavailable in the Excel worksheet. That was my first VBA-experience.
Now, the next step is the one which I’m struggling to find a solution for. I’d like to “connect” this Excel file to Outlook, so that when I click “Send” or enter an e-mail address, a macro:
- checks if the entered e-mail address is in the worksheet of the Excel file
- checks if the present moment falls between the dates of unavailability next to that name
- gives a prompt message when both conditions are met to tell me this person is unavailable, and let me cancel sending the email.
- optional: the message prompt has fields which are filled with the according period of unavailability for that person
Would it be possible to connect Excel to Outlook in this way, so that we can continue using Outlook to write our mails? If it is, some help or a link to an already existing guide would be amazing, as I have failed to find anything viable.
Simplified, it should look something like this:
Click “Send” in Outlook email window
Before sending, call Excel file (does not need to be visible)
Check e-mail address column (column A)
Matches “To” field in Outlook email window?
If No, Send email
If Yes, Check “From” date column (column C) next to corresponding email address
Present date is equal to or later than “From” date?
If No, Send Email
If Yes, Check “Until” Date column (column D): present date is earlier than or equal to present date?
If Yes, message prompt: “Name (column B) is not available from “From” until “Until”. Do you still want to send the e-mail?
Buttons:
Yes: Send Email
No: Close prompt, do not send email, but keep email open.
If No, Send Email
Additionnally here's my code for the userform in the Excel file:
Private Sub CommandDate1_Click()
DatePicker1.Show
AbsencePlannerUserForm.StartTextBox.SetFocus
End Sub
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub ClearButton_Click()
Call AbsencePlannerUserForm_Initialize
End Sub
Private Sub CommandDate2_Click()
DatePicker2.Show
AbsencePlannerUserForm.EndTextBox.SetFocus
End Sub
Private Sub EndTextBox_Change()
End Sub
Private Sub ExtraInfoTextBox_Change()
End Sub
Private Sub OKButton_Click()
Dim M_Date As Date
Dim M_Item As String
M_Date1 = StartTextBox
M_Date2 = EndTextBox
M_Item = EmailTextBox
M_Info = ExtraInfoTextBox
Application.ScreenUpdating = False
LastRow = Abwesenheit1.Cells(Rows.Count, "D").End(xlUp).Row
For rw = 2 To LastRow
If Abwesenheit1.Cells(rw, "A") = M_Item And Cells(rw, "C") = M_Date1
And Cells(rw, "D") = M_Date2 And Cells(rw, "E") = M_Info Then GoTo Passem
Next rw
GoTo NO_Dups
Passem:
Application.ScreenUpdating = True
MsgBox "Der Urlaub für " & M_Item & " vom " & M_Date1 & " bis zum " & M_Date2 & " ist schon eingetragen."
Exit Sub
NO_Dups:
Dim emptyRow As Long
'Make Abwesenheit1 active
Abwesenheit1.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = EmailTextBox.Value
Cells(emptyRow, 3).Value = StartTextBox.Value
Cells(emptyRow, 4).Value = EndTextBox.Value
Cells(emptyRow, 5).Value = ExtraInfoTextBox.Value
Application.Visible = True
Me.Hide
End Sub
Private Sub AbsencePlannerUserForm_Click()
End Sub
Private Sub AbsencePlannerUserForm_Initialize()
'Empty EmailTextBox
EmailTextBox.Value = ""
'Empty StartTextBox
StartTextBox.Value = ""
'Empty EndTextBox
EndTextBox.Value = ""
'Empty ExtraInfoTextBox
ExtraInfoTextBox.Value = ""
'Set Focus on EmailTextBox
EmailTextBox.SetFocus
End Sub
Sub open_form()
Application.Visible = False
UserForm1.Show vbModeless
End Sub
Private Sub StartTextBox_Change()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Application.Visible = True
Me.Hide
End Sub
Looking forward to your more experienced input!
excel vba email outlook prompt
asked Jan 3 at 12:23
GuillaumeGuillaume
11
add a comment |
I am completely new to using VBA and have been searching and using the trial-and-error-method to develop an idea I had to help me and my colleagues. But as I now no longer find online pages to help me further, I’ll just start one myself and hope you’ll be able to help me out!
Context: We mainly work with outsourcing and freelancers, and it is sometimes difficult to keep track of who is unavailable and until when. That’s why I’ve pieced together an Excel file in which a userform simplifies entering dates on which people (entered through their email address) are unavailable in the Excel worksheet. That was my first VBA-experience.
Now, the next step is the one which I’m struggling to find a solution for. I’d like to “connect” this Excel file to Outlook, so that when I click “Send” or enter an e-mail address, a macro:
- checks if the entered e-mail address is in the worksheet of the Excel file
- checks if the present moment falls between the dates of unavailability next to that name
- gives a prompt message when both conditions are met to tell me this person is unavailable, and let me cancel sending the email.
- optional: the message prompt has fields which are filled with the according period of unavailability for that person
Would it be possible to connect Excel to Outlook in this way, so that we can continue using Outlook to write our mails? If it is, some help or a link to an already existing guide would be amazing, as I have failed to find anything viable.
Simplified, it should look something like this:
Click “Send” in Outlook email window
Before sending, call Excel file (does not need to be visible)
Check e-mail address column (column A)
Matches “To” field in Outlook email window?
If No, Send email
If Yes, Check “From” date column (column C) next to corresponding email address
Present date is equal to or later than “From” date?
If No, Send Email
If Yes, Check “Until” Date column (column D): present date is earlier than or equal to present date?
If Yes, message prompt: “Name (column B) is not available from “From” until “Until”. Do you still want to send the e-mail?
Buttons:
Yes: Send Email
No: Close prompt, do not send email, but keep email open.
If No, Send Email
Additionnally here's my code for the userform in the Excel file:
Private Sub CommandDate1_Click()
DatePicker1.Show
AbsencePlannerUserForm.StartTextBox.SetFocus
End Sub
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub ClearButton_Click()
Call AbsencePlannerUserForm_Initialize
End Sub
Private Sub CommandDate2_Click()
DatePicker2.Show
AbsencePlannerUserForm.EndTextBox.SetFocus
End Sub
Private Sub EndTextBox_Change()
End Sub
Private Sub ExtraInfoTextBox_Change()
End Sub
Private Sub OKButton_Click()
Dim M_Date As Date
Dim M_Item As String
M_Date1 = StartTextBox
M_Date2 = EndTextBox
M_Item = EmailTextBox
M_Info = ExtraInfoTextBox
Application.ScreenUpdating = False
LastRow = Abwesenheit1.Cells(Rows.Count, "D").End(xlUp).Row
For rw = 2 To LastRow
If Abwesenheit1.Cells(rw, "A") = M_Item And Cells(rw, "C") = M_Date1
And Cells(rw, "D") = M_Date2 And Cells(rw, "E") = M_Info Then GoTo Passem
Next rw
GoTo NO_Dups
Passem:
Application.ScreenUpdating = True
MsgBox "Der Urlaub für " & M_Item & " vom " & M_Date1 & " bis zum " & M_Date2 & " ist schon eingetragen."
Exit Sub
NO_Dups:
Dim emptyRow As Long
'Make Abwesenheit1 active
Abwesenheit1.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = EmailTextBox.Value
Cells(emptyRow, 3).Value = StartTextBox.Value
Cells(emptyRow, 4).Value = EndTextBox.Value
Cells(emptyRow, 5).Value = ExtraInfoTextBox.Value
Application.Visible = True
Me.Hide
End Sub
Private Sub AbsencePlannerUserForm_Click()
End Sub
Private Sub AbsencePlannerUserForm_Initialize()
'Empty EmailTextBox
EmailTextBox.Value = ""
'Empty StartTextBox
StartTextBox.Value = ""
'Empty EndTextBox
EndTextBox.Value = ""
'Empty ExtraInfoTextBox
ExtraInfoTextBox.Value = ""
'Set Focus on EmailTextBox
EmailTextBox.SetFocus
End Sub
Sub open_form()
Application.Visible = False
UserForm1.Show vbModeless
End Sub
Private Sub StartTextBox_Change()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Application.Visible = True
Me.Hide
End Sub
Looking forward to your more experienced input!
excel vba email outlook prompt
asked Jan 3 at 12:23
GuillaumeGuillaume
11
I am completely new to using VBA and have been searching and using the trial-and-error-method to develop an idea I had to help me and my colleagues. But as I now no longer find online pages to help me further, I’ll just start one myself and hope you’ll be able to help me out!
Context: We mainly work with outsourcing and freelancers, and it is sometimes difficult to keep track of who is unavailable and until when. That’s why I’ve pieced together an Excel file in which a userform simplifies entering dates on which people (entered through their email address) are unavailable in the Excel worksheet. That was my first VBA-experience.
Now, the next step is the one which I’m struggling to find a solution for. I’d like to “connect” this Excel file to Outlook, so that when I click “Send” or enter an e-mail address, a macro:
- checks if the entered e-mail address is in the worksheet of the Excel file
- checks if the present moment falls between the dates of unavailability next to that name
- gives a prompt message when both conditions are met to tell me this person is unavailable, and let me cancel sending the email.
- optional: the message prompt has fields which are filled with the according period of unavailability for that person
Would it be possible to connect Excel to Outlook in this way, so that we can continue using Outlook to write our mails? If it is, some help or a link to an already existing guide would be amazing, as I have failed to find anything viable.
Simplified, it should look something like this:
Click “Send” in Outlook email window
Before sending, call Excel file (does not need to be visible)
Check e-mail address column (column A)
Matches “To” field in Outlook email window?
If No, Send email
If Yes, Check “From” date column (column C) next to corresponding email address
Present date is equal to or later than “From” date?
If No, Send Email
If Yes, Check “Until” Date column (column D): present date is earlier than or equal to present date?
If Yes, message prompt: “Name (column B) is not available from “From” until “Until”. Do you still want to send the e-mail?
Buttons:
Yes: Send Email
No: Close prompt, do not send email, but keep email open.
If No, Send Email
Additionnally here's my code for the userform in the Excel file:
Private Sub CommandDate1_Click()
DatePicker1.Show
AbsencePlannerUserForm.StartTextBox.SetFocus
End Sub
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub ClearButton_Click()
Call AbsencePlannerUserForm_Initialize
End Sub
Private Sub CommandDate2_Click()
DatePicker2.Show
AbsencePlannerUserForm.EndTextBox.SetFocus
End Sub
Private Sub EndTextBox_Change()
End Sub
Private Sub ExtraInfoTextBox_Change()
End Sub
Private Sub OKButton_Click()
Dim M_Date As Date
Dim M_Item As String
M_Date1 = StartTextBox
M_Date2 = EndTextBox
M_Item = EmailTextBox
M_Info = ExtraInfoTextBox
Application.ScreenUpdating = False
LastRow = Abwesenheit1.Cells(Rows.Count, "D").End(xlUp).Row
For rw = 2 To LastRow
If Abwesenheit1.Cells(rw, "A") = M_Item And Cells(rw, "C") = M_Date1
And Cells(rw, "D") = M_Date2 And Cells(rw, "E") = M_Info Then GoTo Passem
Next rw
GoTo NO_Dups
Passem:
Application.ScreenUpdating = True
MsgBox "Der Urlaub für " & M_Item & " vom " & M_Date1 & " bis zum " & M_Date2 & " ist schon eingetragen."
Exit Sub
NO_Dups:
Dim emptyRow As Long
'Make Abwesenheit1 active
Abwesenheit1.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = EmailTextBox.Value
Cells(emptyRow, 3).Value = StartTextBox.Value
Cells(emptyRow, 4).Value = EndTextBox.Value
Cells(emptyRow, 5).Value = ExtraInfoTextBox.Value
Application.Visible = True
Me.Hide
End Sub
Private Sub AbsencePlannerUserForm_Click()
End Sub
Private Sub AbsencePlannerUserForm_Initialize()
'Empty EmailTextBox
EmailTextBox.Value = ""
'Empty StartTextBox
StartTextBox.Value = ""
'Empty EndTextBox
EndTextBox.Value = ""
'Empty ExtraInfoTextBox
ExtraInfoTextBox.Value = ""
'Set Focus on EmailTextBox
EmailTextBox.SetFocus
End Sub
Sub open_form()
Application.Visible = False
UserForm1.Show vbModeless
End Sub
Private Sub StartTextBox_Change()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Application.Visible = True
Me.Hide
End Sub
Looking forward to your more experienced input!
excel vba email outlook prompt
excel vba email outlook prompt
asked Jan 3 at 12:23
GuillaumeGuillaume
11
asked Jan 3 at 12:23
GuillaumeGuillaume
11
edited Jan 4 at 10:47
Guillaume
asked Jan 3 at 12:23
GuillaumeGuillaume
11
asked Jan 3 at 12:23
GuillaumeGuillaume
11
asked Jan 3 at 12:23
GuillaumeGuillaume
11
11
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
This can be done. You will need to create a reference to Outlook through excel in the VBA environment. To do that:
Activate the outlook library from Tools/References

(The number of the library might differ on your computer)
And then call outlook:
https://docs.microsoft.com/en-us/office/vba/outlook/concepts/getting-started/automating-outlook-from-a-visual-basic-application
The you could set a condition and if it matched to show a user form with all the information of unavailability and add button that when clicked exits the sub and closes the outlook message.
If you post the code it will be easier to work on it but basically, call outlook, create a user form with a button to close everything if needed.
answered Jan 3 at 13:37
Pablo GPablo G
736
Hi Pablo, thank you for giving me something to go on. I now activated the outlook object in Excel VBA. Do I now need to write code in Outlook or Excel VBA, when I want Outlook to be my primary tool, and Excel just the source for the data Outlook will use for these conditions? Unfortunately, my Excel VBA code is too long to comment.
– Guillaume
Jan 3 at 14:12
Yes you can do that too. But outlook works a bit different than Excel in terms of macros, a bit more advanced I would say. This is a good website to learn about them: vboffice.net. You will have to do the opposite, call Excel from Outlook but working mainly on the ‘On this session’ module
– Pablo G
Jan 3 at 14:28
Thanks! I'll take a look at it, I'm glad to hear that what I imagined is possible, albeit maybe more advanced than I handle. I'll just give it a try. If it heklps, I inserted the code in my main question.
– Guillaume
Jan 3 at 14:38
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54022233%2fexcel-and-outlook-vba-receive-message-prompt-before-sending-mail-in-outlook-ba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This can be done. You will need to create a reference to Outlook through excel in the VBA environment. To do that:
Activate the outlook library from Tools/References

(The number of the library might differ on your computer)
And then call outlook:
https://docs.microsoft.com/en-us/office/vba/outlook/concepts/getting-started/automating-outlook-from-a-visual-basic-application
The you could set a condition and if it matched to show a user form with all the information of unavailability and add button that when clicked exits the sub and closes the outlook message.
If you post the code it will be easier to work on it but basically, call outlook, create a user form with a button to close everything if needed.
answered Jan 3 at 13:37
Pablo GPablo G
736
Hi Pablo, thank you for giving me something to go on. I now activated the outlook object in Excel VBA. Do I now need to write code in Outlook or Excel VBA, when I want Outlook to be my primary tool, and Excel just the source for the data Outlook will use for these conditions? Unfortunately, my Excel VBA code is too long to comment.
– Guillaume
Jan 3 at 14:12
Yes you can do that too. But outlook works a bit different than Excel in terms of macros, a bit more advanced I would say. This is a good website to learn about them: vboffice.net. You will have to do the opposite, call Excel from Outlook but working mainly on the ‘On this session’ module
– Pablo G
Jan 3 at 14:28
Thanks! I'll take a look at it, I'm glad to hear that what I imagined is possible, albeit maybe more advanced than I handle. I'll just give it a try. If it heklps, I inserted the code in my main question.
– Guillaume
Jan 3 at 14:38
add a comment |
This can be done. You will need to create a reference to Outlook through excel in the VBA environment. To do that:
Activate the outlook library from Tools/References

(The number of the library might differ on your computer)
And then call outlook:
https://docs.microsoft.com/en-us/office/vba/outlook/concepts/getting-started/automating-outlook-from-a-visual-basic-application
The you could set a condition and if it matched to show a user form with all the information of unavailability and add button that when clicked exits the sub and closes the outlook message.
If you post the code it will be easier to work on it but basically, call outlook, create a user form with a button to close everything if needed.
answered Jan 3 at 13:37
Pablo GPablo G
736
Hi Pablo, thank you for giving me something to go on. I now activated the outlook object in Excel VBA. Do I now need to write code in Outlook or Excel VBA, when I want Outlook to be my primary tool, and Excel just the source for the data Outlook will use for these conditions? Unfortunately, my Excel VBA code is too long to comment.
– Guillaume
Jan 3 at 14:12
Yes you can do that too. But outlook works a bit different than Excel in terms of macros, a bit more advanced I would say. This is a good website to learn about them: vboffice.net. You will have to do the opposite, call Excel from Outlook but working mainly on the ‘On this session’ module
– Pablo G
Jan 3 at 14:28
Thanks! I'll take a look at it, I'm glad to hear that what I imagined is possible, albeit maybe more advanced than I handle. I'll just give it a try. If it heklps, I inserted the code in my main question.
– Guillaume
Jan 3 at 14:38
add a comment |
This can be done. You will need to create a reference to Outlook through excel in the VBA environment. To do that:
Activate the outlook library from Tools/References

(The number of the library might differ on your computer)
And then call outlook:
https://docs.microsoft.com/en-us/office/vba/outlook/concepts/getting-started/automating-outlook-from-a-visual-basic-application
The you could set a condition and if it matched to show a user form with all the information of unavailability and add button that when clicked exits the sub and closes the outlook message.
If you post the code it will be easier to work on it but basically, call outlook, create a user form with a button to close everything if needed.
answered Jan 3 at 13:37
Pablo GPablo G
736
This can be done. You will need to create a reference to Outlook through excel in the VBA environment. To do that:
Activate the outlook library from Tools/References

(The number of the library might differ on your computer)
And then call outlook:
https://docs.microsoft.com/en-us/office/vba/outlook/concepts/getting-started/automating-outlook-from-a-visual-basic-application
The you could set a condition and if it matched to show a user form with all the information of unavailability and add button that when clicked exits the sub and closes the outlook message.
If you post the code it will be easier to work on it but basically, call outlook, create a user form with a button to close everything if needed.
answered Jan 3 at 13:37
Pablo GPablo G
736
answered Jan 3 at 13:37
Pablo GPablo G
736
answered Jan 3 at 13:37
Pablo GPablo G
736
answered Jan 3 at 13:37
Pablo GPablo G
736
736
Hi Pablo, thank you for giving me something to go on. I now activated the outlook object in Excel VBA. Do I now need to write code in Outlook or Excel VBA, when I want Outlook to be my primary tool, and Excel just the source for the data Outlook will use for these conditions? Unfortunately, my Excel VBA code is too long to comment.
– Guillaume
Jan 3 at 14:12
Yes you can do that too. But outlook works a bit different than Excel in terms of macros, a bit more advanced I would say. This is a good website to learn about them: vboffice.net. You will have to do the opposite, call Excel from Outlook but working mainly on the ‘On this session’ module
– Pablo G
Jan 3 at 14:28
Thanks! I'll take a look at it, I'm glad to hear that what I imagined is possible, albeit maybe more advanced than I handle. I'll just give it a try. If it heklps, I inserted the code in my main question.
– Guillaume
Jan 3 at 14:38
add a comment |
Hi Pablo, thank you for giving me something to go on. I now activated the outlook object in Excel VBA. Do I now need to write code in Outlook or Excel VBA, when I want Outlook to be my primary tool, and Excel just the source for the data Outlook will use for these conditions? Unfortunately, my Excel VBA code is too long to comment.
– Guillaume
Jan 3 at 14:12
Yes you can do that too. But outlook works a bit different than Excel in terms of macros, a bit more advanced I would say. This is a good website to learn about them: vboffice.net. You will have to do the opposite, call Excel from Outlook but working mainly on the ‘On this session’ module
– Pablo G
Jan 3 at 14:28
Thanks! I'll take a look at it, I'm glad to hear that what I imagined is possible, albeit maybe more advanced than I handle. I'll just give it a try. If it heklps, I inserted the code in my main question.
– Guillaume
Jan 3 at 14:38
Hi Pablo, thank you for giving me something to go on. I now activated the outlook object in Excel VBA. Do I now need to write code in Outlook or Excel VBA, when I want Outlook to be my primary tool, and Excel just the source for the data Outlook will use for these conditions? Unfortunately, my Excel VBA code is too long to comment.
– Guillaume
Jan 3 at 14:12
Hi Pablo, thank you for giving me something to go on. I now activated the outlook object in Excel VBA. Do I now need to write code in Outlook or Excel VBA, when I want Outlook to be my primary tool, and Excel just the source for the data Outlook will use for these conditions? Unfortunately, my Excel VBA code is too long to comment.
– Guillaume
Jan 3 at 14:12
Yes you can do that too. But outlook works a bit different than Excel in terms of macros, a bit more advanced I would say. This is a good website to learn about them: vboffice.net. You will have to do the opposite, call Excel from Outlook but working mainly on the ‘On this session’ module
– Pablo G
Jan 3 at 14:28
Yes you can do that too. But outlook works a bit different than Excel in terms of macros, a bit more advanced I would say. This is a good website to learn about them: vboffice.net. You will have to do the opposite, call Excel from Outlook but working mainly on the ‘On this session’ module
– Pablo G
Jan 3 at 14:28
Thanks! I'll take a look at it, I'm glad to hear that what I imagined is possible, albeit maybe more advanced than I handle. I'll just give it a try. If it heklps, I inserted the code in my main question.
– Guillaume
Jan 3 at 14:38
Thanks! I'll take a look at it, I'm glad to hear that what I imagined is possible, albeit maybe more advanced than I handle. I'll just give it a try. If it heklps, I inserted the code in my main question.
– Guillaume
Jan 3 at 14:38
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54022233%2fexcel-and-outlook-vba-receive-message-prompt-before-sending-mail-in-outlook-ba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
